Geek Answers Handbook

ORA-00904 invalid identifier for nested selection

I am stuck with this error for 2 hours.

I have a nested selection 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 tried many things, none of them seem to help me.

+4
source share
2 answers

You cannot use the parent table in an internal sub-query. Here's 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 for tmp_table

+4
source

The problem is that you can only pass a link from an external request to the next level of the subquery.

Here are a few alternatives:

select tbl.table_name,
       (select min(column_name) keep (dense_rank first order by tstamp asc)
        from   log_table_b l
        where  tbl.assignment_no = l.rpt_no
       ) as "USERNAME"
from   prod_table tbl;

select tbl.table_name,
       l.username
from   prod_table tbl
       inner join (select rpt_no,
                          min(column_name) keep (dense_rank first order by tstamp asc) username
                   from   log_table_b l
                   group by rpt_no)
         on (tbl.assignment_no = l.rpt_no);

NB unverified

0

More articles:


All Articles