RE: Problem with joining char field to varchar2 field

From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 4 May 2004 21:20:23 -0400
I don't know how it works on Windows but it works right on AIX:

Nonpadded Comparison Semantics
Oracle compares two values character by character up to the first
character that differs. The value with the greater character in that
position is considered greater. If two values of different length are
identical up to the end of the shorter one, then the longer value is
considered greater. If two values of equal length have no differing
characters, then the values are considered equal. Oracle uses nonpadded
comparison semantics whenever one or both values in the comparison have
the datatype VARCHAR2 or NVARCHAR2

Waleed

-----Original Message-----
From: Warren Homer [mailto:wazhomer@xxxxxxxxxxx]
Sent: Tuesday, May 04, 2004 8:51 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Problem with joining char field to varchar2 field=20


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=20
where both are defined as NOT NULL. Although these two fields have the
same=20
value inserted Oracle interprets these two columns to be NOT equal. I
have=20
tried the same SQL test on a Window 2000 Oracle 9.2.0.3.0 environment=20
without any problems. Is this a bug in Oracle 9201 or a problem with AIX
?=20
Thanks in advance for any help.

The following shows the process I have used to create the problem.

CREATE TABLE PTMBT01
   (COL1_ID             NUMBER(10)      NOT NULL
  , COL2                CHAR(30)        NOT NULL
   )
TABLESPACE PSMBT01_00
PCTFREE 5
PCTUSED 75;

CREATE TABLE PTMBT02
   (COL1_ID             NUMBER(10)      NOT NULL
  , COL2                VARCHAR2(50)    NOT NULL
   )
TABLESPACE PSMBT02_00
PCTFREE 5
PCTUSED 75;

INSERT INTO PTMBT01 VALUES(1,'111');
INSERT INTO PTMBT01 VALUES(2,'123');

INSERT INTO PTMBT02 VALUES(1,'123');
INSERT INTO PTMBT02 VALUES(2,'123');


the following SQL returns NO rows. However, it does return two rows (as=20
expected) when run in the Windows 2000 Oracle 9.2.0.3.0 environment.

SELECT *
FROM PTMBT01 T01
   , PTMBT02 T02
WHERE T01.COL2 =3D T02.COL2;


Thanks in advance,
Warren.

_________________________________________________________________
Mother's Day is May 9. Make it special with great ideas from the
Mother's=20
Day Guide! http://special.msn.com/network/04mothersday.armx

----------------------------------------------------------------
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
-----------------------------------------------------------------
RE: Problem with joining char field to varchar2 field

Other related posts:
