error while Retrieving the subquery column in where condition to main query

Question

I am trying to retrieve a column in sub query which is used in where condition in Oracle SQL.  I am facing ORA-00904 invalid identifier error. Below is the query. Can someone please resolve.

trying to retrieve ev.type , ev.fil_path in the sub-query select ev.type , ev.fil_path,ev.test_path from effortvariation ev

Query:

select
ev.fil_path,
ev.tes_path,
sp.uname,
sp.pwd,
s.desc,
sp.option,
oq.master_id,
oq.status
from onlineQuery oq
inner join supporttask s
on oq.sq =s.seq
inner join securityports sp
on sp.sq=oq.sq
where sp.type in (select ev.type , ev.fil_path,ev.test_path from effortvariation ev
where ev.team =(select team from allTeams where id ='k238')
and ev.name ='qa'
)
and sp.acct='active'

Answer 1

Hello @Jyra,

You should first pay attention to error message, what is Oracle trying to say here. Invalid identifier means the column name entered is either missing or invalid

Solution:

Enter a valid column name. In Oracle database, a valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.