oracle - convert Select inside DECODE to SQL

Question

i have Oracle SQL like this :

SELECT 
           DOU$2.CUSTCD AS CUSTCD, 
           DOU$2.CHUNO AS CHUNO, 
           DOU$2.LINNO AS LINNO, 
           DOU$2.SHIPDAYYM AS SHIPDAYYM, 
           sum(DOU$2.NOUKN) AS NOUKN, 
           sum(DOU$2.ZEIKN) AS ZEIKN, 
           sum(
              CASE DOU$2.ZEIRITSU
                 WHEN 
                    (
                       SELECT V_KZEIRITSU.ZEIRITSU
                       FROM SDNISHI.V_KZEIRITSU
                    ) THEN DOU$2.NOUKN
                 ELSE 0
              END) AS NOUKN2
           
        FROM 
           SDNISHI.T_HCHUMON_DOUSOU  AS DOU$2 
              INNER JOIN SDNISHI.SY_KANRI  AS KNR 
              ON KNR.SHIPDAYYM = DOU$2.SHIPDAYYM 
              INNER JOIN SDNISHI.T_HCHUMON_MEI  AS MEI 
              ON 
                 MEI.CUSTCD = DOU$2.CUSTCD AND 
                 MEI.CHUNO = DOU$2.CHUNO AND 
                 MEI.LINNO = DOU$2.LINNO AND 
                 MEI.SHIPDAYYM = DOU$2.SHIPDAYYM AND 
                 MEI.USEDNGKBN = '0' AND 
                 MEI.CANCELKBN = '0' 
              LEFT OUTER JOIN SDNISHI.T_HCHUMON_HD  AS HD 
              ON 
                 HD.CUSTCD = MEI.CUSTCD AND 
                 HD.CHUNO = MEI.CHUNO AND 
                 HD.LINNO = MEI.LINNO AND 
                 HD.USEDNGKBN = '0' AND 
                 HD.CANCELKBN = '0' AND 
                 isnull(HD.CANKBN, '00') = '00'
        WHERE 
           DOU$2.USEDNGKBN = '0' AND 
           DOU$2.CANCELKBN = '0' AND 
           ((
           MEI.CHGDELKBN = '1' AND 
           MEI.HDOUSOUKBN = '02' AND 
           (MEI.CHUSU > 0 OR MEI.BCHUSU > 0)) OR (
           MEI.CHGDELKBN != '1' AND 
           HD.HDOUSOUKBN = '02' AND 
           (MEI.CHKBTNFGA = '1' AND HD.CHUSU > 0) OR (MEI.CHKBTNFGB = '1' AND HD.BCHUSU > 0)))
        GROUP BY 
           DOU$2.CUSTCD, 
           DOU$2.CHUNO, 
           DOU$2.LINNO, 
           DOU$2.SHIPDAYYM

That is code is created by SSMA(Migrate from Oracle to SQL) and it cannot run.

I already tried to convert like this :

sum(
              CASE 
                 WHEN 
                   DOU$2.ZEIRITSU in  (
                       SELECT V_KZEIRITSU.ZEIRITSU
                       FROM SDNISHI.V_KZEIRITSU
                    ) THEN DOU$2.NOUKN
                 ELSE 0
              END) AS NOUKN2

But this give me error :

Cannot perform an aggregate function on an expression containing an aggregate or a subquery

Can anyone help me please

Answer 1

Your inner select returns a table. That can't be used as parameter to match a WHERE IN condition. Instead try using an INNER JOIN

sum(decode(
    select sum(dou.noukn)
    from dou
    join v_kzeiritsu on 
         dou.zeiritsu = v_kzeiritsu.zeiritsu
)) as noukn2;