不是Group By表达式Error
我有这样的
SQL
;
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
但是我得到了这个错误。
ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:
我不能把这个代码放到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)
我不明白错误!!
没有找到相关结果
已邀请:
4 个回复
臀夯脖锑
子句。 我认为问题是你在查询中的两个级别引用
:在最顶级,
,但也在提供分组值的计算中:
为了做到这一点,这非常棘手。 我认为解决此问题的最简单方法是从嵌套内联查询构建查询。我已经用三个级别重写了你的查询。最里面的查询 -
- 选择数据,包括没有任何聚合的函数。中间查询 -
- 计算总和。最外层的查询适用于舍入和其他事物;这应该根据您现有的查询返回结果。
显然,这可能无法编译 - 我没有检查您的数据库架构。逻辑也可能是错误的;我不得不对你的业务逻辑做出假设。但是,我认为这更容易理解,并且更容易调试。
杭难插
这不是聚合函数。只有SUM部分在那里。使用分析功能。
淑灯
昧伎
引用了一个select语句 - 并且您不能在group by子句中使用select语句。