Re: Problem with joining char field to varchar2 field

From: Tim Gorman <tim_at_sagelogix.com>
Date: Tue, 04 May 2004 19:24:46 -0600
Message-ID: <BCBD9E7E.145C7%tim@sagelogix.com>

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
-----------------------------------------------------------------

Received on Tue May 04 2004 - 20:21:31 CDT