Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Problem with joining char field to varchar2 field

Problem with joining char field to varchar2 field

From: Warren Homer <wazhomer_at_hotmail.com>
Date: Wed, 05 May 2004 10:50:49 +1000
Message-ID: <Sea2-F35dLFYxc9NUpu00000f99@hotmail.com>


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.

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 expected) when run in the Windows 2000 Oracle 9.2.0.3.0 environment.

SELECT *
FROM PTMBT01 T01
   , PTMBT02 T02
WHERE T01.COL2 = T02.COL2; Thanks in advance,
Warren.



Motherís Day is May 9. Make it special with great ideas from the Motherís 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
-----------------------------------------------------------------
Received on Tue May 04 2004 - 19:48:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US