Not a Group By expression错误
Not a Group By expression错误
提问于 2011-04-05 14:21:13
我有一个这样的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
Stack Overflow用户
回答已采纳
发布于 2011-04-05 14:30:32
ANSI SQL statndard的一个非常恼人的特性是，任何不是聚合的列都需要包含在GROUP BY子句中。这是一个典型的愚蠢的重复，当它是显而易见的时候(我相信MySQL允许我们忽略标准的这一部分)。
所以，我担心它的意思是，您需要在GROUP BY子句中包含CASE()子句。
我认为问题在于您在查询中的两个级别引用了SUM()：既在最顶层SUM(B.RISK)，也在提供分组值的计算中：
( 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这是一个相当棘手的问题。
我认为解决这个问题的最简单方法是从嵌套的内联查询构建查询。我已经用三个层次重写了你的查询。最里面的查询- IQ -选择数据，包括没有任何聚合的函数。中间的查询- SQ -计算总和。最外层的查询应用四舍五入和其他内容；这应该会根据现有查询返回结果。
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
/显然，这可能无法编译--我还没有您的数据库模式可供检查。此外，逻辑可能是错误的；我不得不对您的业务逻辑进行假设。然而，我确实认为这更容易理解，也更容易调试。
Stack Overflow用户
发布于 2011-04-05 14:41:11
, 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 ) 这不是聚合函数。只有总和部分在那里。使用Analytic functions。
Stack Overflow用户
发布于 2011-04-05 14:38:55
首先，下次你可能想要发布一个完整的例子-当尝试运行你的查询时，我得到了至少4个未定义的函数和2个不存在的表的错误(S_TEKLIF，GECIKME_CV，ipotek，risk_cv，m_v，mv_ex -在那之后我放弃了)。
第二:尝试将其分成两个查询，如下所示
select mus_k_isim, hesap_no, my_key, ... from
(select 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) my_key
from ...
) group by mus_k_isim, hesap_no, my_key页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/5547992
复制相关文章
相似问题
腾讯云开发者
