RE: Problem with joining char field to varchar2 field
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 5 May 2004 09:49:29 -0400
Just checked 8.1.5 on NT and 10g on XP.
In both cases default setting for "blank_trimming" is FALSE.
But, it could be different in Warren's installation.
Igor Neyman, OCP DBA
ineyman@xxxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Goulet, Dick
Sent: Wednesday, May 05, 2004 8:35 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Problem with joining char field to varchar2 field
Warren,
There is a structural difference between a varchar and char
datatypes. =
Char's will get padded to the full size of the field whereas Varchar's =
won't. To explain the differences in Windoze vs. AIX check the init =
file to see if BLANK_TRIMMING is true. Something in the past tells me =
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@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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
Other related posts:
