Re: Problem with joining char field to varchar2 field

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 http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: