7

I'm trying to UNION the results of two queries. But I'm getting the following error:

Error at Command Line:9 Column:81
Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"

Here's my query:

SELECT application_id, clicks, datee, client_id FROM(
(select 
    APPL_CD AS application_id, 
    count(*) as clicks, 
    to_date((to_char(ACTN_TAKE_DATA_TM, 'dd-mm-yyyy')), 'dd-mm-yyyy') as datee, 
    ALRT_RSPNS_FROM_CLIENT_ID AS client_id 
from  ALRT_PLATFORM_ALRT_HSTRY
    where  ACTN_TAKE_CD is not null 
    group by to_char(ACTN_TAKE_DATA_TM, 'dd-mm-yyyy'), APPL_CD, ALRT_RSPNS_FROM_CLIENT_ID order by datee) 
UNION ALL
(select 
    APPL_CD AS application_id, 
    count(*) as clicks, 
    to_date((to_char(ACTN_TAKE_DATA_TM, 'dd-mm-yyyy')), 'dd-mm-yyyy') as datee, 
    ALRT_RSPNS_FROM_CLIENT_ID AS client_id 
from  ALRT_PLATFORM_ALRT
    where  ACTN_TAKE_CD is not null 
    group by to_char(ACTN_TAKE_DATA_TM, 'dd-mm-yyyy'), APPL_CD, ALRT_RSPNS_FROM_CLIENT_ID order by datee )
)
Bob
  • 5,510
  • 9
  • 48
  • 80
jai
  • 21,519
  • 31
  • 89
  • 120

1 Answers1

13

Remove the ORDER BY from the nested queries:

SELECT application_id, clicks, datee, client_id FROM(
(select 
    APPL_CD AS application_id, 
    count(*) as clicks, 
    to_date((to_char(ACTN_TAKE_DATA_TM, 'dd-mm-yyyy')), 'dd-mm-yyyy') as datee, 
    ALRT_RSPNS_FROM_CLIENT_ID AS client_id 
from  ALRT_PLATFORM_ALRT_HSTRY
    where  ACTN_TAKE_CD is not null 
    group by to_char(ACTN_TAKE_DATA_TM, 'dd-mm-yyyy'), APPL_CD, ALRT_RSPNS_FROM_CLIENT_ID) 
UNION ALL
(select 
    APPL_CD AS application_id, 
    count(*) as clicks, 
    to_date((to_char(ACTN_TAKE_DATA_TM, 'dd-mm-yyyy')), 'dd-mm-yyyy') as datee, 
    ALRT_RSPNS_FROM_CLIENT_ID AS client_id 
from  ALRT_PLATFORM_ALRT
    where  ACTN_TAKE_CD is not null 
    group by to_char(ACTN_TAKE_DATA_TM, 'dd-mm-yyyy'), APPL_CD, ALRT_RSPNS_FROM_CLIENT_ID )
)
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • @cedar715: because `Oracle` does not support `ORDER BY` in the nested queries. The resulting order is not guaranteed anyway. – Quassnoi Mar 04 '10 at 16:12
  • WHY?! I wanted to combine two pre-ordered result set so that set A appears above set B (figuratively). But I can only apply ORDER BY for set A. If I try to apply ORDER BY on set B, it says 'missing right parenthesis'. Why is it allowed on set A then? How can I have both sets ordered individually without introducing an ordering key to do final ordering with? – ADTC May 03 '13 at 06:50
  • Never mind, I figured that if you nest the query like `SELECT * FROM ( /* set B query with ORDER BY */ )` you can have set B ordered, and it won't give the error :D – ADTC May 03 '13 at 07:02
  • @ADTC: without final `ORDER BY`, the final ordering is not guaranteed, even if both your sets are ordered. In fact, if they are both ordered, the optimizer will likely choose `MERGE UNION` method which will not preserve "A then B" order. – Quassnoi May 03 '13 at 08:05
  • @Quassnoi thanks. Is there a correct way to preserve the "A then B" order? In my tests of `UNION ALL` it seems to preserve it and not mix it up, but that can just be a coincidence (the Explain Plan doesn't mention `MERGE UNION`). Maybe I could do conditional sorting as a final `ORDER BY` instead of sorting in subqueries, yea? – ADTC May 03 '13 at 08:44
  • 1
    @ADTC: yes, that's exactly what you should do. – Quassnoi May 03 '13 at 09:05
  • 1
    @ADTC: see here: http://sqlfiddle.com/#!6/8fedc/1. I forced the optimizer to use `MERGE UNION`, and, as you can see, the tables' records are all mixed up. `ORDER BY` is the only guaranteed way to order results. – Quassnoi May 03 '13 at 11:22
  • Thanks for the clarification. I had decided to do final ordering using `ORDER BY` to guarantee the order. With some tricks like `ORDER BY CASE WHEN ~ THEN 1 ELSE 0 END ASC`, I can get the correct ordering I desire `:D` It's all good now. Thank you for your kind help! – ADTC May 07 '13 at 08:01