SQL> create table a ( N1 number );
Table created.
SQL> create table b (c1 varchar2(50));
Table created.
SQL> insert into a values (1000);
1 row created.
SQL> insert into a values (2000);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into b values ('1000');
1 row created.
SQL> insert into b values('abcd');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from a, b where a.n1 = b.c1;
select * from a, b where a.n1 = b.c1
*
ERROR at line 1:
ORA-01722: invalid number=== the order in which the comparison is coded doesn't matter:
SQL> select * from a, b where b.c1 = a.n1;
select * from a, b where b.c1 = a.n1
*
ERROR at line 1:
ORA-01722: invalid number=== but you can explicitly cast the number as a varchar2, then the comparison succeeds:
SQL> select * from a, b where b.c1 = cast(a.n1 as varchar2(50));
N1 C1
---------- --------------------------------------------------
1000 10001 row selected.
=== or if you have other predicates which filter out offensive values before the comparison it works as well:
SQL> select * from a, b where b.c1 = a.n1 and b.c1 < 'a';
N1 C1
---------- --------------------------------------------------
1000 10001 row selected.
The latter could be the reason the view sometimes works.
At 12:49 PM 9/9/2003 -0800, you wrote:
I don't know what to think re. this.
There is a view here that produces an error, I identified why -- in one AND clause a number(9) datatype column is being joined with a varchar2(50) datatype column.
The developer of this code says that this used to run, there must be something wrong with the server.
I want to verify... Is there any kind of overloading invoked automatically when Oracle compares columns of different datatypes?
i.e. if the varchar2(50) column only contains numbers, would Oracle convert it automatically to number before making the comparison?
(My intuition says: NO. )
Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
