Solved

invalid number in a select statement

Posted on 2008-09-30
4
816 Views
Last Modified: 2012-05-05
The following query is okay.  
    SELECT code_desc  FROM  dm_codes WHERE   code in   ( SELECT   emp_no FROM emp ) ;

But, the following query result in invalid number.  Why?  Thanks.
            select ( SELECT code_desc   FROM  dm_codes    WHERE  code  = emp_no )  
                             from     ( SELECT   emp_no FROM emp  ) ;


                           
0
Comment
Question by:ewang1205
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
paquicuba earned 250 total points
ID: 22607274
It looks like you have a column named EMP_NO in dm_codes as well that is a VARCHAR2 datatype, so need to qualify your subselect in order for it to work:

select ( SELECT code_desc   FROM  dm_codes    WHERE  code  = x.emp_no )  
                             from     ( SELECT   emp_no FROM emp  )  x;
 
0
 

Author Comment

by:ewang1205
ID: 22607871
Tried.  That is not where the problem is.  
0
 
LVL 20

Assisted Solution

by:gatorvip
gatorvip earned 250 total points
ID: 22608993
What is the error that you are getting?
0
 

Author Comment

by:ewang1205
ID: 22609029
The following is working.  I will split the points between you two.  Thanks.
 
select ( SELECT code_desc   FROM  dm_codes    WHERE  code  =  to_char(emp_no  ) )  
                             from     ( SELECT   emp_no FROM emp  ) ;
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question