ORA-00904 invalid identifier for nested select

Question

I have been stuck on this error for 2 hours.

I have nested select to get the first value.

select tbl.table_name,
       (select distinct(FirstItem)
       from
           (select first_value(column_name) over (order by timestamp asc rows unbounded predecing) as FirstItem
           from log_table_b l
           where tbl.assignment_no = l.rpt_no)
       ) as "USERNAME",
from prod_table tbl;

It returns this error:

ERROR at line 6:
ORA-00904: "TBL"."ASSIGNMENT_NO": invalid identifier

I have tried many things, none of them seems to be helping me.

Answer 1

You can't use the parent table in inner sub query. Here how you could achieve this :

with tmp_table as 
(
    select rpt_no, first_value(column_name) over (order by timestamp asc rows unbounded predecing) as FirstItem
    from log_table_b l
) select distinct tbl.table_name, firstItem
from prod_table tbl
join tmpTable on tmp_table.rpt_no = tbl.assignment_no;

You might want to find a more descriptive name to tmp_table