CODEWITHSUNDEEP
sqloracle-database
devcoder112
devcoder112

Reputation: 161

UNION ALL query in Oracle results in an error

I cannot figure why I am getting this error message in Oracle. Please see the error message below. I am using a UNION ALL and both queries are working (the top and bottom ones).

Also, both queries have the same headers. So, I am not sure why this union all is not working and getting an error message. I keep trying to figure it out but I am not getting anywhere.

Error Message 

ORA-00933: SQL command not properly ended
Error at Line: 13 Column: 23

Code

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

Upvotes: 1

Views: 954

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

You order the overall result set, not each branch of the union; so you have to get rid of the first order by clause:

...
or b.DATE1 >= TO_DATE('2018-01-01','YYYY-MM-DD'))
union all
select a.ID1, a.ID2, cnt, a.*, j.*
...
order by ID2 asc

This is mentioned in the documentation:

You cannot specify the order_by_clause in the subquery of these operators.

Incidentally, if you're supplying fixed dates you can use date literals, which are a bit less typing:

or b.DATE1 >= DATE '2018-01-01')

And as @Littlefoot pointed out, this order-by clause will throw "ORA-00960: ambiguous column naming in select list" because you have a.ID1, a.ID2, ... a.* which means the ID1 and ID2 columns will appear twice in the select list, and Oracle doesn't know which ID1 reference you mean (they're the same value here, but needn't be). It isn't good practice to use .* - much better to list the columns you actually need.

It's also a bad idea to mix ANSI joins (as in the second branch) with old-style joins (as in your first branch); you may be getting away with it here but it can confuse the optimiser, as well as anyone else looking at the code, and even this could break some day. You've used (+) but not consistently so that will act as an inner join anyway; but if you want an outer join then write it as a proper ANSI one.

Upvotes: 3

Nico Gatti
Nico Gatti

Reputation: 1

Try using order by only at the end of last query and remove it from the first one when doing the union all.

Upvotes: 0

Related Questions