Sorry I need to hide

Question

Elon Reeve Musk FRS is an entrepreneur and business magnate. He is the founder, CEO, and Chief Engineer at SpaceX; early-stage investor, CEO, and Product Architect of Tesla, Inc.; founder of The Boring Company; and co-founder of Neuralink and OpenAI.

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;

Answer 2

Just move your sum logic inside select as follows:

 (SELECT SUM(DOU$2.NOUKN) 
   FROM SDNISHI.V_KZEIRITSU V
  WHERE DOU$2.ZEIRITSU = V.ZEIRITSU) AS NOUKN2

In case If it gives aggregation error then use sum(above query) AS NOUKN2

Answer 3

Your code is very strange. For instance, it seems to assume that V_KZEIRITSU has one row. But, you can move this to the FROM clause:

SELECT SUM(CASE WHEN DOU.ZEIRITSU = K.ZEIRITSU THEN DOU.NOUKN ELSE 0 END)  AS NOUKN2
FROM DOU LEFT JOIN
     V_KZEIRITSU K
     ON 1=1   -- in case the table is really empty

A slightly more reasonable version would be:

SELECT SUM(DOU.NOUKN) AS NOUKN2
FROM DOU LEFT JOIN
     V_KZEIRITSU K
     ON DOU.ZEIRITSU = K.ZEIRITSU  -- in case the table is really empty

It seems rather unlikely to me that this is what you really intend. If not, I would suggest that you ask a new question with appropriate same data, desired results, and explanation of the results. A non-working query should not be expected to provide the same level of information.

Answer 4

I'd say that it is, actually, as simple as

select sum(dou.noukn) 
from dou
where dou.zeiritsu in (select zeiritsu from v_kzeiritsu)

(I'm not sure what dou is (table? alias?), but I hope you do.)

---

After you edited the question, I'm editing the answer. I marked with "--> this" two lines that - in my opinion - might help. As previously, the whole sum(case ...) as noukn2 is replaced by a simple sum(dou$2.noukn).

Note that - in Oracle - you can't use as keyword for table alias. For example:

• no: from employees as e
• yes: from employees e

---

Here's your query:

  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 (dou$2.noukn) AS noukn2                          --> this
    FROM SDNISHI.T_HCHUMON_DOUSOU DOU$2
         INNER JOIN SDNISHI.SY_KANRI KNR ON KNR.SHIPDAYYM = DOU$2.SHIPDAYYM
         INNER JOIN SDNISHI.T_HCHUMON_MEI 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 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'
         JOIN v_keziritsu vk ON vk.zeiritsu = dou$2.zeiritsu  --> this
   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