RE: Problem with joining char field to varchar2 field

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Tue, 4 May 2004 21:20:23 -0400
Message-ID: <42BBD772AC30EA428B057864E203C99911590C@MSGBOSCLF2WIN.DMN1.FMR.COM>

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_at_hotmail.com] Sent: Tuesday, May 04, 2004 8:51 PM
To: oracle-l_at_freelists.org
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_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


-----------------------------------------------------------------

----------------------------------------------------------------

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:18:18 CDT