0

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')
Vivek
  • 461
  • 1
  • 3
  • 13

1 Answers1

0

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')
jose_bacoy
  • 12,227
  • 1
  • 20
  • 38
  • I am trying to understand why we can only use this subquery in a join even when the results of the subquery is scalar(having one column value for one row). So if you take out the `*` and replace it with `id` in the column, I still run into the same issue. Please review the OP update. – Vivek Apr 03 '18 at 11:27
  • the subquery is in a firm of a table (temp) so you need to join it to make use of it. – jose_bacoy Apr 03 '18 at 11:42