CODEWITHSUNDEEP
sqloracle-database
Lane
Lane

Reputation: 115

Vague ORA-00904 Invalid Identifier error

I have a query where I build a table of values and I union it with another query. I wish to return a set of results where one value between the 2 tables does not match. (query example below). Every time I try to execute, I get the dreaded invalid identifier error, but I have no idea why. Any suggestions would be appreciated!

select * from (
select '1234567' as empno, 'A' as status, 'Active' as st_name from dual union all
select '89012345' as empno, 'DA' as status, 'Inactive' as st_name from dual) ft
union 
select id,status,statusnm from second_table st
where st.id = ft.empno
and st.statusnm <> ft.st_name;

Any thoughts/advice?

Upvotes: 0

Views: 222

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You don't want union, you want join . . . and it should be explicit:

select st.*
from (select '1234567' as empno, 'A' as status, 'Active' as st_name from dual   
      union all
      select '89012345' as empno, 'DA' as status, 'Inactive' as st_name from dual
     ) ft join
     second_table st 
     on st.id = ft.empno and st.statusnm <> ft.st_name

Upvotes: 1

Related Questions