Home » SQL & PL/SQL » SQL & PL/SQL » How to compare the Varchar and Number in oracle10g
How to compare the Varchar and Number in oracle10g [message #307468] Tue, 18 March 2008 23:42 Go to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
We faced a problem while upgrading the Database from 9i to 10g which is mentioned below.
In Oracle 9i we can compare the varchar and number to check conditions , But this is not supported in 10G.

For example

Table Column DataType

Finished_Voyage Finished_year VARCHAR2
Container_Cost Voyage_Year NUMBER

finished_voyage.finished_year = container_cost.voyage_year this condition can be done in Oracle 9i, where we will get the output.
But in 10G this will not be supported also it will not throw any error and where we won't get the expected output.so pls give me a proper suggestion to resolve this issue

Thanks,
Re: How to compare the Varchar and Number in oracle10g [message #307495 is a reply to message #307468] Wed, 19 March 2008 00:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You should never rely on implicit datatype conversion (not in 10G, not in 9i).
Convert the number to a string by using to_char with the proper format mask.
Re: How to compare the Varchar and Number in oracle10g [message #307496 is a reply to message #307468] Wed, 19 March 2008 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NEVER rely on implicit conversion.

Regards
Michel
Re: How to compare the Varchar and Number in oracle10g [message #307555 is a reply to message #307496] Wed, 19 March 2008 02:06 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
Can u give me the correct solution with an example pls

Thanks,
Re: How to compare the Varchar and Number in oracle10g [message #307558 is a reply to message #307555] Wed, 19 March 2008 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank gave it: use TO_CHAR on one column or TO_NUMBER on the other one.

Regards
Michel
Re: How to compare the Varchar and Number in oracle10g [message #307565 is a reply to message #307555] Wed, 19 March 2008 02:33 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
I have following tables to compare the varchar datatype and number

create table empl
(id number(9),adds number(9))

insert into empl
values(10,100);

create table empls
(id vrachar2(4), name varchar2(20))

insert into empls
values(10,'100');
insert into empls values(20,'ram');

select id,name
from empls a
where (id) = (select id
from empl b
where a.id =b.id);
This brings the correct O/P

select id,name
from empls a
where (id,name) = (select id,adds
from empl b
where a.id =b.id);

this brings invalid number

select id,name
from empls a
where (id,name) IN (select id,to_char(adds)
from empl b
where a.id =b.id);

This query gives me no rows return in 10g
Re: How to compare the Varchar and Number in oracle10g [message #307566 is a reply to message #307565] Wed, 19 March 2008 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Compare strings with strings and numbers with numbers.

I got the correct result:
SQL> select id,name
  2  from empls a
  3  where (id,name) IN (select id,to_char(adds)
  4  from empl b
  5  where a.id =b.id);
ID   NAME
---- --------------------
10   100

1 row selected.


By the way, the test case you posted is plenty of syntax errors.

Regards
Michel

[Updated on: Wed, 19 March 2008 02:41]

Report message to a moderator

Re: How to compare the Varchar and Number in oracle10g [message #307568 is a reply to message #307565] Wed, 19 March 2008 02:41 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Post like what I did .
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> create table empl
  2  (id number(9),adds number(9));

Table created.

SQL> insert into empl
  2  values(10,100);

1 row created.

SQL> create table empls
  2  (id vrachar2(4), name varchar2(20));
(id vrachar2(4), name varchar2(20))
            *
ERROR at line 2:
ORA-00907: missing right parenthesis


SQL> ed
Wrote file afiedt.buf

  1  create table empls
  2* (id varchar2(4), name varchar2(20))
SQL> /

Table created.

SQL> insert into empls
  2  values(10,'100');

1 row created.

SQL> insert into empls values(20,'ram');

1 row created.

SQL> select id,name
  2  from empls a
  3  where (id) = (select id
  4  from empl b
  5  where a.id =b.id);

ID   NAME
---- --------------------
10   100

**** Don't do this....
SQL> select id,name
  2  from empls a
  3  where (id,name) = (select id,adds
  4  from empl b
  5  where a.id =b.id);

ID   NAME
---- --------------------
10   100

SQL> select id,name
  2  from empls a
  3  where (id,name) IN (select id,to_char(adds)
  4  from empl b
  5  where a.id =b.id);

ID   NAME
---- --------------------
10   100

Regards

Raj
Re: How to compare the Varchar and Number in oracle10g [message #307601 is a reply to message #307568] Wed, 19 March 2008 04:32 Go to previous message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
Thanks a lot for your kind replys
regards
Ram
Previous Topic: Urgent Help Required!! PL/SQL Table Doubt
Next Topic: TRIGGER ISSUES
Goto Forum:
  


Current Time: Fri Dec 09 02:00:43 CST 2016

Total time taken to generate the page: 0.09961 seconds