Re: Problem with joining char field to varchar2 field
- From: Natural Join B.V. <lex.de.haan@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 5 May 2004 09:31:10 MET
Hi Tim,
allow me to elaborate a little here.
if you are referring to what I think you are referring to, this is not an
ANSI/ISO bug but rather a feature ;-)
it is not a matter of what you are physically storing in your database, but
rather a matter of comparison semantics when dealing with strings of different
lengths -- the two options are: padded vs non-padded semantics.
Cheers,
Lex.
PS: adding leading zero's to a numeric attribute does not change its value;
adding trailing spaces to an alphanumeric attribute DOES change its value.
> 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
> -----------------------------------------------------------------
>
----------------------------------------------------------------
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:
