Re: Problem with joining char field to varchar2 field
- From: "Carel-Jan Engel" <cjpengel.dbalert@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 5 May 2004 11:22:29 +0200 (CEST)
Hi Lex,
When it is a feature, it is intended to behave that way. I agree with you
that adding spaces does change the value of a string. Warren detected
different behaviour on different platforms (and versions of the RDBMS). I
remember similar problems with SQL*Menu (5.0?) in the days that varchar
got replaced by varchar2. Warrens description depicts that Oracle
considers the values equal on 9.2.0.3 on Windows, but unequal on 9.2.0.1
on AIX. Has the intention changed between 9.2.0.1 and 9.2.0.3, or is the
comparison function OS-dependent? (or both?) To be sure this has to be
tested with 9.2.0.3 on AIX. I have no access to that combination, but I'm
getting quite curious.
Regards, Carel-Jan
===
If you think education is expensive, try ignorance. (Derek Bok)
===
> Hi Tim,
>
> allow me to elaborate a little here.
> if you are referring to what I think you are referring to, this is not an
> ANSI/ISO bug but rather a feature ;-)
>
>
> Cheers,
> Lex.
>
> PS: adding leading zero's to a numeric attribute does not change its
> value;
> adding trailing spaces to an alphanumeric attribute DOES change its value.
>
>> Warren,
>>
>> This is a bug with the ANSI SQL standard.
>>
>>
>> There is no practical justification for the CHAR and NCHAR datatypes;
>> storing trailing spaces in the database as much sense as consuming
>> storage
>> to store leading zero's in a numeric. These datatypes exist only to
>> ensure
>> that Oracle doesn't get dinged by competitors that it doesn't meet the
>> full
>> ANSI standard.
>>
>> Hope this helps...
>>
>> -Tim
>>
>>
>> on 5/4/04 6:50 PM, Warren Homer at wazhomer@xxxxxxxxxxx wrote:
>>
>> > 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.
----------------------------------------------------------------
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:
