ORA-00904 invalid identifier for nested select

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, firstItemfrom prod_table tbljoin tmpTable on tmp_table.rpt_no = tbl.assignment_no;

You might want to find a more descriptive name to tmp_table

The issue is that you can only pass a reference from an outer query down to the next subquery level.

Here are a couple of 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.usernamefrom   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);

N.B. untested