Group and alliance in the oracle
I would like to combine 2 queries but ran into 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;
The two requests are completely individual. But when using union it says ORA-00904: "RESPONSECOUNT": Invalid identifier
source to share
The error you are facing
In Oracle, it is best to always specify each column in each subquery UNION
in the same way. The following should work in your case:
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 problem 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 request you really wanted
From your comments, however, I suspect you meant to write a completely 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;
source to share
The union column names are determined using the first query . So your first column is actually called FAULTCOUNT
.
But the simplest way to sort the result of a join is to use a column index:
select ...
union
select ...
order by 1;
You probably also want to use UNION ALL
, which avoids removing duplicates between two requests and is faster than normalUNION
source to share