CODEWITHSUNDEEP
sqloracle-database
devcoder112
devcoder112

Reputation: 161

UNION ALL, ORDER BY at the end, invalid identifier, ascending, error message

I am getting another error message. Please see the below error message. Here I am doing a UNION ALL and I can confirm that this query works without the ORDER BY at the end. However, I do need to order by that column at the end. So, I am not sure how to fix this issue. I even tried removing the second query a. to a1. so they have different aliases. I now know to put ORDER BY at the end of the code when doing a UNION ALL but did not expect to get invalid identifier.

Error Message 

ORA-00904: "A1"."ID1": invalid identifier
Error at Line: 1 Column: 678

Code

select a.ID2, a.ID1, cnt, a.*, b.*
from (
select t.*, 
count(distinct ID2) over(partition by ID1) cnt 
from TABLE1 t) a
, TABLE2 b 
where a.cnt > 1
and a.ID2=b.ID2 (+)
and (b.STATUS in ('A','L','P','S') 
or b.DATE1 >= TO_DATE('2018-01-01','YYYY-MM-DD'))
union all
select a1.ID2, a1.ID1, cnt, a1.*, j.*
from (
select t.*, 
count(distinct ID2) over(partition by ID1) cnt 
from TABLE1 t) a1
inner join TABLE2 j
on a1.ID2=j.ID2
inner join SCHEMA1.TABLE3 d
on j.ALTER_ID2=d.ALTER_ID2
where a1.cnt > 1
order by a.ID1 asc, a1.ID1 asc

Upvotes: 0

Views: 662

Answers (1)

dhiman
dhiman

Reputation: 517

Use ORDER BY 2, 1 asc -- order by column number

ORDER BY is executed after the complete SQL is executed.

Upvotes: 2

Related Questions