| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_hotmail.com 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_at_freelists.org
> 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
> -----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed May 05 2004 - 02:28:09 CDT
 |
 |