alias table with union all not works

Question

I have ora-00094 (identifier not valid) in a simple query but I can't see why. Could you help me please?

select columnA, 'More than 4000 bytes'
from tableA

union all

select  p.columnB, listagg(p.columnC, ',') within group (order by p.columnC)
from (            
  select distinct b.job_name, a.hostname
  from tableB a, emuser.def_job b
) p   
group by p.columnB 
order by p.columnB desc;

score 1 · Accepted Answer · answered Nov 21 '17 at 08:07

ORDER BY is for ResultSet of whole query. So for ORDER BY there is no columnB here. ResultSet have only column names of first query.

Try this

SELECT columnA, 'More than 4000 bytes' as columnC  FROM tableA
UNION ALL
  SELECT p.columnB, LISTAGG (p.columnC, ',') WITHIN GROUP (ORDER BY p.columnC)
    FROM (SELECT DISTINCT b.job_name, a.hostname
            FROM tableB a, emuser.def_job b) p
GROUP BY p.columnB
ORDER BY p.columnA DESC;

alias table with union all not works

1 Answers1