invalid number in a select statement

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  ) ;


                           
ewang1205Asked:
Who is Participating?
 
paquicubaCommented:
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
 
ewang1205Author Commented:
Tried.  That is not where the problem is.  
0
 
gatorvipCommented:
What is the error that you are getting?
0
 
ewang1205Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.