I have a very complex query -
SELECT
' ' AS object_type,
ROWNUM AS object_pri_key_1,
collected_count AS object_pri_key_2,
total_count AS object_pri_key_3,
'COLLECTED' object_data,
0 AS version_no,
0 AS modification_no,
'ESAF031002' login_id,
progress_color text_1,
dpd text_2,
' ' text_3,
' ' text_4,
' ' text_5,
' ' text_6,
' ' text_7,
' ' text_8,
' ' text_9,
' ' text_10,
' ' is_active,
' ' pw_session_id,
sysdate device_create_timestamp,
sysdate device_modify_timestamp,
'Y' AS rec_sync_status,
to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') AS rec_sync_timestamp,
'' AS lead_error
FROM
(
SELECT
decode(seq, 1, 'DPD (1-30)', 2, 'DPD (31-60)',
3, 'DPD (61-80)', 4, 'DPD (81-90)', 5,
'DPD (>90)') AS dpd,
cnt total_count,
0 collected_count,
0 collected_total_percentage,
0 total_amount,
0 amount_collected,
0 collected_amount_percentage,
'#00695c' AS progress_color,
seq
FROM
(
SELECT
emp_id,
nvl(dpd, 0) dpd,
COUNT(1) cnt,
1 seq
FROM
vw_loan_details
WHERE
nvl(dpd, 0) BETWEEN 1 AND 30
GROUP BY
emp_id,
dpd
UNION
SELECT
emp_id,
nvl(dpd, 0) dpd,
COUNT(1) cnt,
2 seq
FROM
vw_loan_details
WHERE
nvl(dpd, 0) BETWEEN 31 AND 60
GROUP BY
emp_id,
dpd
UNION
SELECT
emp_id,
nvl(dpd, 0) dpd,
COUNT(1) cnt,
3 seq
FROM
vw_loan_details
WHERE
nvl(dpd, 0) BETWEEN 61 AND 80
GROUP BY
emp_id,
dpd
UNION
SELECT
emp_id,
nvl(dpd, 0) dpd,
COUNT(1) cnt,
4 seq
FROM
vw_loan_details
WHERE
nvl(dpd, 0) BETWEEN 81 AND 90
GROUP BY
emp_id,
dpd
UNION
SELECT
emp_id,
nvl(dpd, 0) dpd,
COUNT(1) cnt,
5 seq
FROM
vw_loan_details
WHERE
nvl(dpd, 0) > 0
GROUP BY
emp_id,
dpd
)
WHERE
emp_id IN (
SELECT DISTINCT
emp_id
FROM
vw_emp_co
WHERE
branch_code IN (
SELECT
column_value
FROM
TABLE ( get_emp_branch_code('ESAF031002') )
)
AND emp_type IN (
SELECT
view_data
FROM
tb_emp_type
WHERE
emp_type = 'I'
)
AND ( ( emp_id = (
CASE
WHEN 'Collection Officer' IN ( 'Collection Officer', 'Field Executive' ) THEN
'ESAF031002'
ELSE
' '
END
) )
OR 1 = (
CASE
WHEN 'Collection Officer' NOT IN ( 'Collection Officer', 'Field Executive' ) THEN
1
ELSE
0
END
) )
GROUP BY
seq, decode(seq, 1, 'DPD (1-30)', 2, 'DPD (31-60)',
3, 'DPD (61-80)', 4, 'DPD (81-90)', 5,
'DPD (>90)'), '#00695c'
-- order by seq
)
);
In the query I am getting error -
ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" *Cause: *Action: Error at Line: 111 Column: 21
I have tried everything but I am unable to debug it (this code is not my and I can't contact the owner that is why I am posting it here)
I am unable to find why I am getting this not a group by expression error.
The error indicates that in a GROUP BY clause a column is referenced that is not in the SELECT clause. If you can't find which one, then break the select into chunks and run them one by one to see which one errors out - the way your select is organised this should be pretty straightforward. Or check each of the GROUP BY clauses and check if the columns are referenced.
The most likely candidate is the last inner query. The statement
GROUP BY
seq, decode(seq, 1, 'DPD (1-30)', 2, 'DPD (31-60)',
3, 'DPD (61-80)', 4, 'DPD (81-90)', 5,
'DPD (>90)'), '#00695c'
-- order by seq
is part of the query starting with:
SELECT DISTINCT
emp_id
FROM
vw_emp_co
There is no column seq in that select clause so this should error out with a "not a group by expression" error.
However, that group by is not needed in that last select and neither is the DISTINCT keyword - both will just impact the subquery result (and be more costly) but not the result of the emp_id IN (<subquery>) clause. The easiest fix is to just remove the GROUP BY clause above.