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'
ORA-00904: "NAME": invalid identifier
00904. 00000 - "%s: invalid identifier"
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';