4

I would like to union 2 queries but facing an error in oracle.

select count(*) as faultCount,
       COMP_IDENTIFIER 
from CORDYS_NCB_LOG 
where AUDIT_CONTEXT='FAULT' 
union 
select count(*) as responseCount,
       COMP_IDENTIFIER 
from CORDYS_NCB_LOG 
where AUDIT_CONTEXT='RESPONSE' 
group by COMP_IDENTIFIER  
order by responseCount;

Two queries run perfectly individually.but when using union,it says ORA-00904: "RESPONSECOUNT": invalid identifier

eshaa
  • 386
  • 2
  • 7
  • 26
  • 1
    You are creating a resulset with a column with the count. You are defining 2 names for the same column. There is a correct answer, I'm just wondering if you want a single column or 2 columns. – user_0 Jun 08 '15 at 14:51
  • i need 2 columns faultCount,responseCount – eshaa Jun 08 '15 at 14:55

3 Answers3

6

The error you've run into

In Oracle, it's best to always name each column in each UNION subquery the same way. In your case, the following should work:

select count(*) as theCount,
       COMP_IDENTIFIER 
from CORDYS_NCB_LOG 
where AUDIT_CONTEXT='FAULT' 
group by COMP_IDENTIFIER -- don't forget this
union 
select count(*) as theCount,
       COMP_IDENTIFIER 
from CORDYS_NCB_LOG 
where AUDIT_CONTEXT='RESPONSE' 
group by COMP_IDENTIFIER  
order by theCount;

See also:

Curious issue with Oracle UNION and ORDER BY

A good workaround is, of course, to use indexed column references as suggested by a_horse_with_no_name

The query you really wanted

From your comments, however, I suspect you wanted to write an entirely different query, namely:

select count(case AUDIT_CONTEXT when 'FAULT'    then 1 end) as faultCount,
       count(case AUDIT_CONTEXT when 'RESPONSE' then 1 end) as responseCount,
       COMP_IDENTIFIER 
from CORDYS_NCB_LOG 
where AUDIT_CONTEXT in ('FAULT', 'RESPONSE')
group by COMP_IDENTIFIER  
order by responseCount;
Community
  • 1
  • 1
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
3

The column names of a union are determined by the first query. So your first column is actually named FAULTCOUNT.

But the easiest way to sort the result of a union is to use the column index:

select ...
union 
select ...
order by 1;

You most probably also want to use UNION ALL which avoids removing duplicates between the two queries and is faster than a plain UNION

  • *"The column names of a union are determined by the first query"* - In fact, that's not entirely correct (for Oracle). It may well be that there is no available column name in `ORDER BY`. [This might just be a bug, though](http://stackoverflow.com/q/25387951/521799) – Lukas Eder Jun 08 '15 at 14:56
  • I need the resultset columns to be faultCount,responseCount,COMP_IDENTIFIER – eshaa Jun 08 '15 at 14:57
  • Then you don't want a UNION. UNION says build one data set with the data from the second query tacked onto the end of the data set from the first query - and line up the columns. JOINs are for putting together two queries onto the same row. – Bill Jun 08 '15 at 15:01
  • @user1650864 The union only has two columns. And *one* column can only have *one* name. You can't name the first column `FAULTCOUNT` **and** `COMP_IDENTIFIER` at the same time. –  Jun 08 '15 at 15:08
  • hi,Can you post the query u r trying to explian – eshaa Jun 08 '15 at 15:13
  • @user1650864: I'm trying to explain _your_ query. –  Jun 08 '15 at 15:14
0

In Union or Union all query column names are determined by the first query column name.

In your query replace "order by responseCount" with "order by faultCount.

sujata
  • 16
  • 1