无法在包含聚合或子查询的表达式上执行聚合函数(当获取CASE语句返回的列的和时)。

Question

下面的SQL查询有问题。在执行时，它会产生错误：

Msg 130，15级，状态1，第2行 无法对包含聚合或子查询的表达式执行聚合函数。

SELECT 
sum(case when DESP_SCANNING.PALLET_NO != null then (select PALLETNO_MASTER.PALLET_WEIGHT from PALLETNO_MASTER where DESP_SCANNING.PALLET_NO= PALLETNO_MASTER.PALLET_NO and DESP_SCANNING.REGION_ID = PALLETNO_MASTER.REGION_ID) else (select CARTONNO_MASTER.CARTON_WEIGHT from CARTONNO_MASTER where DESP_SCANNING.CARTON_NO = CARTONNO_MASTER.CARTON_NO and DESP_SCANNING.REGION_ID = CARTONNO_MASTER.REGION_ID) end) as totweight

FROM CUSTOMER_MASTER, DESP_SCANNING, CARRIER_MASTER, DC_MASTER

WHERE CARRIER_MASTER.CARRIER_CODE = DESP_SCANNING.CARRIER_CODE AND 

((DESP_SCANNING.PALLET_NO != null and DESP_SCANNING.PALLET_NO = (select PALLETNO_MASTER.PALLET_NO from PALLETNO_MASTER where DESP_SCANNING.REGION_ID = PALLETNO_MASTER.REGION_ID)) or(DESP_SCANNING.PALLET_NO = null and DESP_SCANNING.CARTON_NO = (select CARTONNO_MASTER.CARTON_NO from CARTONNO_MASTER where DESP_SCANNING.REGION_ID = CARTONNO_MASTER.REGION_ID))) AND

DESP_SCANNING.STATUS = 2 

group by DESP_SCANNING.DC_NO, DESP_SCANNING.POD_DATE, CUSTOMER_MASTER.CUS_NAME, CARRIER_MASTER.CARRIER_NAME, DESP_SCANNING.AWB_NO,DC_MASTER.DESP_END_DATE_TIME, DESP_SCANNING.SCAN_DATE, DC_MASTER.PSR_LOAD_DATE_TIME

order by CARRIER_MASTER.CARRIER_NAME, DESP_SCANNING.POD_DATE

错误显示在这一行中：

sum(case when DESP_SCANNING.PALLET_NO != null then (select PALLETNO_MASTER. PALLET_WEIGHT from PALLETNO_MASTER where DESP_SCANNING.PALLET_NO= PALLETNO_MASTER.PALLET_NO and DESP_SCANNING.REGION_ID = PALLETNO_MASTER.REGION_ID) else (select CARTONNO_MASTER.CARTON_WEIGHT from CARTONNO_MASTER where DESP_SCANNING.CARTON_NO = CARTONNO_MASTER.CARTON_NO and DESP_SCANNING.REGION_ID = CARTONNO_MASTER.REGION_ID) end) as totweight, 

所以，我能给你什么而不是这个。

Answer 1

看来你得考虑一下这个案子..。构造(它具有导致错误消息的子查询)到WHERE子句中。

也就是说，首先在单个列中构造一个包含PALLET_WEIGHT或CARTON_WEIGHT的表，并在该列上进行和。类型选择和(X)从(你的大复杂的选择在这里)。

)我不能保证这是可能的。这正是我想要做的，阅读错误信息告诉我的内容。比我大的产品专家可能会告诉你我的想法也行不通。)

Answer 2

也许这段代码能帮到你：

select sum(x.totweight) as totweight
from (

select  
        CARRIER_MASTER.CARRIER_NAME, 
        DESP_SCANNING.POD_DATE,
        case when DESP_SCANNING.PALLET_NO != null then(
                      select  PALLETNO_MASTER.PALLET_WEIGHT
                      from    PALLETNO_MASTER
                      where   DESP_SCANNING.PALLET_NO = PALLETNO_MASTER.PALLET_NO
                              and DESP_SCANNING.REGION_ID = PALLETNO_MASTER.REGION_ID
                  )
             else(
                      select  CARTONNO_MASTER.CARTON_WEIGHT
                      from    CARTONNO_MASTER
                      where   DESP_SCANNING.CARTON_NO = CARTONNO_MASTER.CARTON_NO
                              and DESP_SCANNING.REGION_ID = CARTONNO_MASTER.REGION_ID
                  )
        end as totweight
from    CUSTOMER_MASTER,
        DESP_SCANNING,
        CARRIER_MASTER,
        DC_MASTER
where   CARRIER_MASTER.CARRIER_CODE = DESP_SCANNING.CARRIER_CODE
        and (
            (
                DESP_SCANNING.PALLET_NO != null
                and DESP_SCANNING.PALLET_NO = (
                    select  PALLETNO_MASTER.PALLET_NO
                    from    PALLETNO_MASTER
                    where   DESP_SCANNING.REGION_ID = PALLETNO_MASTER.REGION_ID
                )
            )
            or (
                DESP_SCANNING.PALLET_NO = null
                and DESP_SCANNING.CARTON_NO = (
                    select  CARTONNO_MASTER.CARTON_NO
                    from    CARTONNO_MASTER
                    where   DESP_SCANNING.REGION_ID = CARTONNO_MASTER.REGION_ID
                )
            )
        )
        and DESP_SCANNING.status = 2
group by    DESP_SCANNING.DC_NO,
            DESP_SCANNING.POD_DATE,
            CUSTOMER_MASTER.CUS_NAME,
            CARRIER_MASTER.CARRIER_NAME,
            DESP_SCANNING.AWB_NO,
            DC_MASTER.DESP_END_DATE_TIME,
            DESP_SCANNING.SCAN_DATE,
            DC_MASTER.PSR_LOAD_DATE_TIME
) x
order by x.CARRIER_NAME, x.POD_DATE