ParaJump ParaJump - 16 days ago 5
SQL Question

Concatenate and use in where clause oracle plsql

I have to concatenate two fields and use concatenated field in where clause but it gives me invalid identifier. How to solve this query.

select i.FIRST_NAME || ' - ' || i.LAST_NAME as NAME, i.* from CONTACT i
where NAME = 'JOHN - HANKS'


This gives me

ORA-00904: "NAME": invalid identifier
00904. 00000 - "%s: invalid identifier"

Answer

You cannot use a column alias at the same level. Just use a subquery (or repeat the expression):

select c.*
from (select i.FIRST_NAME || ' - ' || i.LAST_NAME as NAME, i.*
      from CONTACT i 
     ) c
where c.NAME = 'JOHN - HANKS';