I have a SQL like this;
SELECT B.MUS_K_ISIM AS CUSTOMER_NAME, B.HESAP_NO AS CUSTOMER_NO,
SUM(B.RISK) AS TOTAL_RISK,
(CASE WHEN B.DOVIZ_KOD = 21 THEN 'EUR' WHEN B.DOVIZ_KOD = 2 THEN 'USD' WHEN B.DOVIZ_KOD = 1 THEN 'TL' END) AS CCY,
ROUND(RISK_CV(:TAR,B.HESAP_NO,B.DOVIZ_KOD),2) AS TOTAL_RISK_EUR,
ROUND(SUM(MV_EX(B.TEKLIF_NO1,B.TEKLIF_NO2,:TAR)),2) AS RESALE_VALUE_OLD,
ROUND(SUM(M_V(B.TEKLIF_NO1,B.TEKLIF_NO2,:TAR)),2) AS RESALE_VALUE_NEW,
NVL(IPOTEK(B.HESAP_NO),0) AS SECURITIES,
NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 0, 30, B.DOVIZ_KOD),2),0) AS BUCKET1,
NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 30, 60, B.DOVIZ_KOD),2),0) AS BUCKET2,
NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 60, 90, B.DOVIZ_KOD),2),0) AS BUCKET3,
NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 90, 9999, B.DOVIZ_KOD),2),0) AS BUCKET4,
(CASE WHEN GECIKME_CV(:TAR,B.HESAP_NO, 90, 9999, B.DOVIZ_KOD) > 0 THEN 100 WHEN B.HESAP_NO IN (SELECT HESAP_NO FROM S_TAKIP_MUSTERI) THEN 100 ELSE 0 END) AS PROV,
(CASE WHEN B.HESAP_NO IN (SELECT HESAP_NO FROM S_TAKIP_MUSTERI) THEN 'E' ELSE 'H' END) AS CAT5,
ROUND(RISK_CV(:TAR,B.HESAP_NO,B.DOVIZ_KOD)-SUM(M_V(B.TEKLIF_NO1,B.TEKLIF_NO2,:TAR))-NVL(IPOTEK(B.HESAP_NO),0),2) AS NET_PROV,
(CASE WHEN (RISK_CV(:TAR,B.HESAP_NO,B.DOVIZ_KOD)-SUM(M_V(B.TEKLIF_NO1,B.TEKLIF_NO2,:TAR))-NVL(IPOTEK(B.HESAP_NO),0))<0 OR
NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 90, 9999, B.DOVIZ_KOD),2),0) <= 0 THEN 0
ELSE ROUND((RISK_CV(:TAR,B.HESAP_NO,B.DOVIZ_KOD)-SUM(M_V(B.TEKLIF_NO1,B.TEKLIF_NO2,:TAR))-NVL(IPOTEK(B.HESAP_NO),0)),2) END) AS CORR_PROV
FROM S_TEKLIF B
WHERE NVL(B.RISK,0) > 0
--AND (GECIKME_CV(:TAR,B.HESAP_NO, 60, 90, B.DOVIZ_KOD) > 0 OR GECIKME_CV(:TAR,B.HESAP_NO, 90, 9999, B.DOVIZ_KOD) > 0)
GROUP BY B.MUS_K_ISIM, B.HESAP_NO, (CASE WHEN B.DOVIZ_KOD = 21 THEN 'EUR' WHEN B.DOVIZ_KOD = 2 THEN 'USD' WHEN B.DOVIZ_KOD = 1 THEN 'TL' END), ROUND(RISK_CV(:TAR,B.HESAP_NO,B.DOVIZ_KOD),2), NVL(IPOTEK(B.HESAP_NO),0), NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 0, 30, B.DOVIZ_KOD),2),0), NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 30, 60, B.DOVIZ_KOD),2),0), NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 60, 90, B.DOVIZ_KOD),2),0), NVL(ROUND(GECIKME_CV(:TAR,B.HESAP_NO, 90, 9999, B.DOVIZ_KOD),2),0), (CASE WHEN GECIKME_CV(:TAR,B.HESAP_NO, 90, 9999, B.DOVIZ_KOD) > 0 THEN 100 WHEN B.HESAP_NO IN (SELECT HESAP_NO FROM S_TAKIP_MUSTERI) THEN 100 ELSE 0 END), (CASE WHEN B.HESAP_NO IN (SELECT HESAP_NO FROM S_TAKIP_MUSTERI) THEN 'E' ELSE 'H' END)
ORDER BY B.MUS_K_ISIM
But i getting this error.
ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:
I can't put this code to GROUP BY?
(CASE WHEN GECIKME_CV(:TAR,B.HESAP_NO, 90, 9999, B.DOVIZ_KOD) > 0 THEN 100 WHEN B.HESAP_NO IN (SELECT HESAP_NO FROM S_TAKIP_MUSTERI) THEN 100 ELSE 0 END)
I don't understand the error!!
It is a really annoying feature of the ANSI SQL statndard that any column which is not an aggregate needs to be included in the GROUP BY clause. This is a classic piece of stupid duplication, when it's obvious (I believe MySQL allows us to ignore this part of the standard).
So, I'm afraid what it means is, you need to include the CASE() clause in the GROUP BY clause.
I think the problem is that you reference SUM() at two levels in the query: both at the toppermost level, SUM(B.RISK) but also in the calculations which provide grouping values:
( CASE WHEN ( RISK_CV ( :TAR , B.HESAP_NO , B.DOVIZ_KOD )
- SUM ( M_V ( B.TEKLIF_NO1 , B.TEKLIF_NO2 , :TAR ) )
- NVL ( IPOTEK ( B.HESAP_NO ) , 0 ) ) < 0
This is pretty tricky to get right.
I think the easiest way to resolve this is to build up the query from nested inline queries. I have re-written your query with three levels. The innermost query - IQ - selects the data, including the functions without any aggregations. The middle query - SQ - calculates the sums. The outermost query applies rounding and other things; this should return the results as per your existing query.
SELECT sq.MUS_K_ISIM AS CUSTOMER_NAME,
sq.HESAP_NO AS CUSTOMER_NO,
sq.TOTAL_RISK,
(CASE WHEN sq.DOVIZ_KOD = 21 THEN 'EUR'
WHEN sq.DOVIZ_KOD = 2 THEN 'USD'
WHEN sq.DOVIZ_KOD = 1 THEN 'TL' END) AS CCY,
ROUND(sq.RISK_EUR,2) AS TOTAL_RISK_EUR,
ROUND(sq.RESALE_VALUE_OLD,2) AS RESALE_VALUE_OLD,
ROUND(sq.RESALE_VALUE_OLD,2) AS RESALE_VALUE_NEW,
sq.SECURITIES,
NVL(ROUND(sq.BUCKET1,2),0) AS BUCKET1,
NVL(ROUND(sq.BUCKET2,2),0) AS BUCKET2,
NVL(ROUND(sq.BUCKET3,2),0) AS BUCKET3,
NVL(ROUND(sq.BUCKET4,2),0) AS BUCKET4,
(CASE WHEN sq.BUCKET4,2) > 0 THEN 100
WHEN sq.CAT5 = 'H' THEN 100
ELSE 0 END) AS PROV,
sq.CAT5,
sq.NET_PROV,
(CASE WHEN sq.NET_PROV <0
OR
sq.RISK_EUR <= 0 THEN 0
ELSE ROUND(sq.NET_PROV,2) END) AS CORR_PROV
from (
select
iq.MUS_K_ISIM ,
iq.HESAP_NO ,
iq.DOVIZ_KOD,
iq.RISK_EUR,
iq.SECURITIES,
iq.BUCKET1,
iq.BUCKET2,
iq.BUCKET3,
iq.BUCKET4,
iq.CAT5
sum(iq.RISK) as TOTAL_RISK,
sum(iq.RESALE_VALUE_OLD) as RESALE_VALUE_OLD,
sum(iq.RESALE_VALUE_NEW) as RESALE_VALUE_NEW,
iq.RISK_EUR - sum(iq.RESALE_VALUE_NEW) - iq.SECURITIES) AS NET_PROV
from (
SELECT B.MUS_K_ISIM ,
B.HESAP_NO ,
B.RISK,
B.DOVIZ_KOD,
RISK_CV(:TAR,B.HESAP_NO,B.DOVIZ_KOD) AS RISK_EUR,
MV_EX(B.TEKLIF_NO1,B.TEKLIF_NO2,:TAR) AS RESALE_VALUE_OLD,
M_V(B.TEKLIF_NO1,B.TEKLIF_NO2,:TAR) AS RESALE_VALUE_NEW,
NVL(IPOTEK(B.HESAP_NO),0) AS SECURITIES,
GECIKME_CV(:TAR,B.HESAP_NO, 0, 30, B.DOVIZ_KOD) AS BUCKET1,
GECIKME_CV(:TAR,B.HESAP_NO, 30, 60, B.DOVIZ_KOD) AS BUCKET2,
GECIKME_CV(:TAR,B.HESAP_NO, 60, 90, B.DOVIZ_KOD) AS BUCKET3,
GECIKME_CV(:TAR,B.HESAP_NO, 90, 9999, B.DOVIZ_KOD) AS BUCKET4,
nvl2(M.HESAP_NO, 'E', 'H') AS CAT5
from
FROM S_TEKLIF B
left outer join S_TAKIP_MUSTERI on (B.HESAP_NO = M.HESAP_NO)
WHERE NVL(B.RISK,0) > 0
) iq
group by
iq.MUS_K_ISIM ,
iq.HESAP_NO ,
iq.DOVIZ_KOD,
iq.RISK_EUR,
iq.SECURITIES,
iq.BUCKET1,
iq.BUCKET2,
iq.BUCKET3,
iq.BUCKET4,
iq.CAT5
)sq
ORDER BY sq.MUS_K_ISIM
/
Obviously, this may not compile - I haven't got your database schema to check against. Also the logic may be wrong; I have had to make assumptions about your business logic. However, I do think this is easier to understand and will be easier to get debug.