1

I am trying to combine two SQL queries into one using a UNION clause. However, I am somehow stuck with the error message ORA-00933: SQL command not properly ended on the line of the UNION operator.

The only difference between the two queries is an additional check in the WHERE clause in one of the statements and different b.typeOf values being queried. The rest is 100% identical.

I don't really understand why Oracle expects the SQL command to end right before the UNION operator, I mean the only reason these operators exist is to combine queries... Why would it expect the query to end before combining it with the following statement :/

The only thing I can think of is that it is having problems with the GROUP BY and/or ORDER BY clauses in the statements. However, these are vital, so I can't just remove them and try it again, as the data wouldn't make any sense without it.

Does anyone know an explanation for this behavior and a possible fix for it? Trying to locate similar problems on SO or Google didn't yield much yet unfortunately. Any hints are appreciated.

Here's my SQL:

SELECT 'Test' as Name,
extract(Year FROM a.datum) AS Jahr, extract(Month FROM a.datum) AS Monat, count(a.datum) AS  Anzahl_Tickets 
FROM table1 a, table2 b, table2 c 
WHERE a.fk_ID = b.id and b.fk_ID = c.id and b.typeOf in (3,4) 
and (a.isXYZ = 0 or a.isXYZ is Null or a.ZYX > 0) 
and mod(a.fk_id,2) = 1
and ...
and ...
and ... 
GROUP BY extract(Year FROM a.datum), extract(Month FROM a.datum)
ORDER BY extract(Year FROM a.datum), extract(Month FROM a.datum)

UNION

SELECT 'Test' as Name, 
extract(Year FROM a.datum) AS Jahr, extract(Month FROM a.datum) AS Monat, count(a.datum) AS Anzahl_Tickets 
FROM table1 a, table2 b, table2 c 
WHERE a.fk_ID = b.id and b.fk_ID = c.id and b.typeOf in (0,1) 
and (a.isXYZ = 0 or a.isXYZ is Null or a.ZYX > 0)
-- Notice missing modulo calculation. Thats basically the whole point of splitting this into 2 separate queries.
and ...
and ...
and ... 
GROUP BY extract(Year FROM a.datum), extract(Month FROM a.datum) 
ORDER BY extract(Year FROM a.datum), extract(Month FROM a.datum);

Edit: A new problem now that the UNION works. It doesn't show the results like I intended:

http://i.imgur.com/cBQpbFv.png

Instead of 4 columns I actually expexted 2. Why it doesn't sum 371 + 2 and 1 + 401 into one dataset each?

Edit 2: Nevermind, fixed it by changing the outer clause to:

SELECT Name, JAHR, MONAT, SUM(Anzahl_Tickets) FROM 
(
....
) GROUP BY Name, Jahr, Monat ORDER BY Jahr, Monat;
daZza
  • 1,669
  • 1
  • 29
  • 51
  • Please consider using proper JOINs instead of multiple tables in the FROM clause. – Danny T. Oct 29 '14 at 14:40
  • I am using those when needed, but for these simple ones these implicit joins in the FROM clauses are just more comfortable imho. Results were the same whenever I tested. Or are there any serious drawbacks to it? – daZza Oct 29 '14 at 15:07
  • These two questions are about this : http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause and http://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause – Danny T. Oct 29 '14 at 17:40

2 Answers2

2

I suggest putting the order by outside the subqueries (and changing the UNION to a UNION ALL), like so:

select * from
(SELECT 'Test' as Name,
        extract(Year FROM a.datum) AS Jahr, 
        extract(Month FROM a.datum) AS Monat, 
        count(a.datum) AS  Anzahl_Tickets 
 FROM table1 a, table2 b, table2 c 
 WHERE a.fk_ID = b.id and b.fk_ID = c.id and b.typeOf in (3,4) 
 and (a.isXYZ = 0 or a.isXYZ is Null or a.ZYX > 0) 
 and mod(a.fk_id,2) = 1
 and ...
 and ...
 and ... 
 GROUP BY extract(Year FROM a.datum), extract(Month FROM a.datum)
 UNION ALL
 SELECT 'Test' as Name, 
        extract(Year FROM a.datum) AS Jahr, 
        extract(Month FROM a.datum) AS Monat, 
        count(a.datum) AS Anzahl_Tickets 
 FROM table1 a, table2 b, table2 c 
 WHERE a.fk_ID = b.id and b.fk_ID = c.id and b.typeOf in (0,1) 
 and (a.isXYZ = 0 or a.isXYZ is Null or a.ZYX > 0)
 -- Notice missing modulo calculation. Thats basically the whole point of splitting this into 2 separate queries.
 and ...
 and ...
 and ... 
 GROUP BY extract(Year FROM a.datum), extract(Month FROM a.datum) 
) ORDER BY Jahr, Monat;
0

Remove ORDER BY clause from first subquery or, to save sorting in two subqueries, use something like this:

with t1 as (select 2 a from dual union all select 1 from dual),
     t2 as (select 4 a from dual union all select 3 from dual)

select * from (select * from t1 order by a)
union all 
select * from (select * from t2 order by a);
Dmitriy
  • 5,525
  • 12
  • 25
  • 38