SubQuery within SUM CASE AND IN Conditions

Question

I have this query blow.

SELECT DISTINCT
       'CONTRACT',
       CO.OBJID,
       CO.ORDERNUMBER
       (SELECT SUM(CASE WHEN CT.SHORTNAME = 'BGL'
                        THEN TG.QUANTITY
                        ELSE 0 END) AS Cartons,
               SUM(CASE WHEN CT.SHORTNAME = 'CT'
                        THEN TG.QUANTITY
                        ELSE 0 END) AS Boxes,
               SUM(CASE WHEN CT.SHORTNAME = 'PL'
                        THEN TG.QUANTITY
                        ELSE 0 END) AS Trailer
        FROM   GOODS TG,
               XTYPE CT 
        WHERE  TG.ORDERID = CO.OBJID 
        AND    TG.CONTOBJECTID = CT.OBJID 
        AND    CT.SHORTNAME IN ('BGL', 'CT', 'PL')
       ) 
FROM   XCUSTORDER CO,
       XEDIPARTNER EP
WHERE  CIP.CUSTOID = CO.OBJECTID 
AND    CO.EDIPARTNER_OBJECTID = EP.OBJECTID
AND    EP.ILNNUMBER = 'NASA'

The query is not working how can I achive the result I need. I dont want to run a diffrent select for each of the Cartons, Boxes and Trailer

Answer 1

TRY THIS: if you want to do all in single query. You have to query separately for each column as below. I am not sure that it will work because it's not tested but I am sure it will give you good idea. You are joining with CIP.CUSTOID but the table with CIP alias is not mentioned anywhere in the query so I did EP in the second query.

SELECT DISTINCT
       'CONTRACT',
       CO.OBJID,
       CO.ORDERNUMBER,
       (SELECT SUM(TG.QUANTITY)
        FROM   GOODS TG,
               XTYPE CT 
        WHERE  TG.ORDERID = CO.OBJID 
        AND    TG.CONTOBJECTID = CT.OBJID 
        AND    CT.SHORTNAME IN ('BGL')) AS Cartons,

        (SELECT SUM(TG.QUANTITY)
        FROM   GOODS TG,
               XTYPE CT 
        WHERE  TG.ORDERID = CO.OBJID 
        AND    TG.CONTOBJECTID = CT.OBJID 
        AND    CT.SHORTNAME IN ('CT')) AS Boxes,

        (SELECT SUM(TG.QUANTITY)
        FROM   GOODS TG,
               XTYPE CT 
        WHERE  TG.ORDERID = CO.OBJID 
        AND    TG.CONTOBJECTID = CT.OBJID 
        AND    CT.SHORTNAME IN ('PL')) AS Trailer
FROM   XCUSTORDER CO,
       XEDIPARTNER EP
WHERE  CIP.CUSTOID = CO.OBJECTID 
AND    CO.EDIPARTNER_OBJECTID = EP.OBJECTID
AND    EP.ILNNUMBER = 'NASA'

You can change your query in the following way:

SELECT
    'CONTRACT',
    CO.OBJID,
    CO.ORDERNUMBER,
    SUM(CASE WHEN CT.SHORTNAME = 'BGL'
            THEN TG.QUANTITY
            ELSE 0 END) AS Cartons,
    SUM(CASE WHEN CT.SHORTNAME = 'CT'
            THEN TG.QUANTITY
            ELSE 0 END) AS Boxes,
    SUM(CASE WHEN CT.SHORTNAME = 'PL'
            THEN TG.QUANTITY
            ELSE 0 END) AS Trailer
FROM XCUSTORDER CO
INNER JOIN XEDIPARTNER EP ON  EP.CUSTOID = CO.OBJECTID 
    AND CO.EDIPARTNER_OBJECTID = EP.OBJECTID
    AND EP.ILNNUMBER = 'NASA'
LEFT JOIN GOODS TG ON TG.ORDERID = CO.OBJID
LEFT JOIN XTYPE CT ON TG.CONTOBJECTID = CT.OBJID
    AND CT.SHORTNAME IN ('BGL', 'CT', 'PL')
GROUP BY CO.OBJID,
    CO.ORDERNUMBER