oracle-databasesubquery-factoring

using subquery factoring result in where clause


Why can't I use a subquery factoring clause result in the where clause of as depicted in the following sql:

with rpt as(
 select * from reports where caseid = 
 :case_id and rownum=1 order by created desc
)
select 
 distinct rt.trialid
from 
 report_trials rt
join 
 trial_genes tg on rt.id=tg.trialid
where 
 rt.reportid = rpt.id
and 
tg.gene not in('TMB','MS')

The subquery is named rptand used in the select statement's where clause. When executed encountering the following error: ORA-00904: "RPT"."ID": invalid identifier

UPDATE:

In fact nested query for the same thing is also giving me the same issue. The nested subquery is only returning a single column value from a single row:

select 
 distinct rt.trialid
from 
  report_trials rt
  join 
  trial_genes tg on rt.id=tg.trialid
where 
 rt.reportid = (select id from reports where caseid = :case_id and 
  rownum=1 order by created desc)
and 
 tg.gene not in('TMB','MS')

Solution

  • You missed to add the table rpt in your query, thus that error.

    with rpt as(
     select * from reports where caseid = 
     :case_id and rownum=1 order by created desc
    )
    select 
     distinct rt.trialid
    from 
     report_trials rt
    join 
     trial_genes tg on rt.id=tg.trialid
    join 
      rpt on rt.reportid = rpt.id
    where  
      tg.gene not in('TMB','MS')