RE: Problem with joining char field to varchar2 field

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Wed, 5 May 2004 09:53:13 -0400
Message-ID: <42BBD772AC30EA428B057864E203C99911403C@MSGBOSCLF2WIN.DMN1.FMR.COM>

It does not say in the doc that blank_trimming affects joins. Did you test it?

Regards,

Waleed

-----Original Message-----
From: Goulet, Dick [mailto:DGoulet_at_vicr.com] Sent: Wednesday, May 05, 2004 9:35 AM
To: oracle-l_at_freelists.org
Subject: RE: Problem with joining char field to varchar2 field=20

Warren,

There is a structural difference between a varchar and char datatypes. =
=3D

Char's will get padded to the full size of the field whereas Varchar's =
=3D

won't. To explain the differences in Windoze vs. AIX check the init =
=3D

file to see if BLANK_TRIMMING is true. Something in the past tells me =
=3D

that the default on Windoze is true and Unix is false.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----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=3D20

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) =
=3D

field=3D20
where both are defined as NOT NULL. Although these two fields have the =
=3D

same=3D20
value inserted Oracle interprets these two columns to be NOT equal. I =
=3D

have=3D20
tried the same SQL test on a Window 2000 Oracle 9.2.0.3.0 = environment=3D20
without any problems. Is this a bug in Oracle 9201 or a problem with AIX =
=3D

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

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

Mother's Day is May 9. Make it special with great ideas from the =3D Mother's=3D20
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
-----------------------------------------------------------------
----------------------------------------------------------------
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 Wed May 05 2004 - 08:50:47 CDT