I have a query with case statement in it.
select
COUNTRY_CODE,
CUST_USR_ID,
(
case
when
FACILITY_ID is not null
then
FACILITY_ID
when
ACCOUNT_NO is not null
then
ACCOUNT_CLASS
end
)
ACC_FA_ID, count(1),
(
case
when
FACILITY_ID is not null
then
'FACILITY_ID'
else
'ACCOUNT_CLASS'
end
)
IDENTIFIERS
from
Mytable
where
(
FACILITY_ID is not null
or ACCOUNT_NO is not null
)
group by
COUNTRY_CODE, CUST_USR_ID,
(
case
when
FACILITY_ID is not null
then
FACILITY_ID
when
ACCOUNT_NO is not null
then
ACCOUNT_CLASS
end
)
This query gives me error
ORA-00979: not a GROUP BY expression
When I remove my last case statement from select then it runs fine. Please help me in this
Columns that aren't aggregated should be part of the GROUP BY
clause. It means that "solution"
CASE
from SELECT
, but toGROUP BY
Something like this (CTE is here just to have some sample data in order to show that query works; does it produce what you meant it to, I can't tell):
SQL> WITH mytable (
2 country_code, cust_usr_id, facility_id, account_no, account_class
3 ) AS
4 (SELECT 1, 1, 1, 1, 1 FROM dual UNION ALL
5 SELECT 1, 2, 3, 4, 5 FROM DUAL
6 )
7 SELECT country_code,
8 cust_usr_id,
9 CASE
10 WHEN facility_id IS NOT NULL THEN
11 facility_id
12 WHEN account_no IS NOT NULL THEN
13 account_class
14 END acc_fa_id,
15 COUNT(1),
16 --
17 CASE
18 WHEN facility_id IS NOT NULL THEN
19 'FACILITY_ID'
20 ELSE
21 'ACCOUNT_CLASS'
22 END identifiers
23 FROM mytable
24 WHERE ( facility_id IS NOT NULL
25 OR account_no IS NOT NULL)
26 GROUP BY country_code,
27 cust_usr_id,
28 CASE
29 WHEN facility_id IS NOT NULL THEN
30 facility_id
31 WHEN account_no IS NOT NULL THEN
32 account_class
33 END,
34 CASE
35 WHEN facility_id IS NOT NULL THEN
36 'FACILITY_ID'
37 ELSE
38 'ACCOUNT_CLASS'
39 END;
COUNTRY_CODE CUST_USR_ID ACC_FA_ID COUNT(1) IDENTIFIERS
------------ ----------- ---------- ---------- -------------
1 2 3 1 FACILITY_ID
1 1 1 1 FACILITY_ID
SQL>