Re: Problem with joining char field to varchar2 field
- From: Tim Gorman <tim@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 04 May 2004 19:24:46 -0600
Warren,
This is a bug with the ANSI SQL standard.
Try using the following instead:
SELECT *
FROM PTMBT01 T01,
PTMBT02 T02
WHERE T01.COL2 = RPAD(T02.COL2,30,' ');
Or:
SELECT *
FROM PTMBT01 T01,
PTMBT02 T02
WHERE RTRIM(T01.COL2) = T02.COL2;
There is no practical justification for the CHAR and NCHAR datatypes;
storing trailing spaces in the database as much sense as consuming storage
to store leading zero's in a numeric. These datatypes exist only to ensure
that Oracle doesn't get dinged by competitors that it doesn't meet the full
ANSI standard.
Hope this helps...
-Tim
on 5/4/04 6:50 PM, Warren Homer at wazhomer@xxxxxxxxxxx wrote:
> Hi all,
>
> we are currently running Oracle 9.2.0.1.0 on AIX 4.3.
>
> I am trying to join two tables on a varchar2(50) field and a CHAR(30) field
> where both are defined as NOT NULL. Although these two fields have the same
> value inserted Oracle interprets these two columns to be NOT equal. I have
> tried the same SQL test on a Window 2000 Oracle 9.2.0.3.0 environment
> without any problems. Is this a bug in Oracle 9201 or a problem with AIX ?
> Thanks in advance for any help.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
