SQL conditional for a field using multiple subqueries as cases

Question

I am using Proc SQL, but this question should be relevant for all SQL variants. I am trying to populate a field BruceDPOtest with values from two subqueries with if the first query results in blanks--CASE WHEN BruceDPO = INPUT("", 8.) --it fills that blank with another subquery's BruceDPO value:

THEN (
SELECT SUM(PART_QTY) FROM RSCCParts LEFT JOIN DPO.DPO_PART_ORD_HST AS Total
ON RSCCParts.PartID = STRIP(Total.PART_NO_ID)
WHERE PUT(PROC_DT, YY.) LIKE '%2016%' GROUP BY PART_NO_ID) ELSE BruceDPO END

For example, the first query gives the following results;

Part   DPO

1234   100
1235

The second subquery that references data that can populate the second row is run to get:

Part   DPO

1234   100
1235   999

Here is the full code:

    PROC SQL;
    CREATE VIEW DPOMergeView AS(SELECT *, 
        CASE 
            WHEN BruceDPO = INPUT("", 8.)  THEN (
        SELECT SUM(PART_QTY) FROM RSCCParts LEFT JOIN DPO.DPO_PART_ORD_HST AS Total
            ON RSCCParts.PartID = STRIP(Total.PART_NO_ID)
        WHERE PUT(PROC_DT, YY.) LIKE '%2016%' GROUP BY PART_NO_ID) 
            ELSE BruceDPO 
        END 
    AS BruceDPOtest
        FROM
            RSCCParts
        LEFT JOIN (SELECT RSCCParts.PartID AS BrucePartID, BruceDPO, Year
            FROM RSCCParts
                LEFT JOIN
                    (SELECT PART_NO_ID AS PartNumber, SUM(PART_QTY) AS BruceDPO, STRIP(YR) AS Year
                        FROM
                            DPO.DPO_PART_HST_MAIN
                        WHERE YR = '2016'
                            GROUP BY PartNumber, Year) AS FQuery
                                ON
                                RSCCParts.PartID = STRIP(FQuery.PartNumber)) AS B
                                ON RSCCParts.PartID = B.BrucePartID);
QUIT;

As I run this query, it gets stuck on DATA Step and after 30 minutes, I stopped the query. Am I doing this correctly? If there is a better way to do this please let me know!

We really cannot test this without samples of the data you are combining. Can you post example data for the `RSCCParts`, `DPO.DPO_PART_ORD_HST`, and `DPO.DPO_PART_HST_MAIN` tables? — Tom, Mar 30 '17 at 00:44
This is a general case example. My code is only to show if such a program is possible. You can create a similar program using your own data. Unfortunately I am not allowed to post the data for those tables. — Haris Irshad, Mar 30 '17 at 00:54
No need to post REAL data. Just make up some records that demonstrate the problem. Or create an example using tables like SASHELP.CLASS or SASHELP.CARS that everyone already has available in SAS. — Tom, Mar 30 '17 at 02:57

score 1 · Accepted Answer · answered Mar 30 '17 at 01:01

Normally I avoid correlated subqueries in SQL since it just makes it feel like you are trying to process the data record by record instead of by combining sets. But if you did what to use syntax like

case when (x) then (sub query result) else variable_name end

then the subquery needs to return only one value. Your query

SELECT SUM(PART_QTY) 
FROM RSCCParts LEFT JOIN DPO.DPO_PART_ORD_HST AS Total
ON RSCCParts.PartID = STRIP(Total.PART_NO_ID)
WHERE PUT(PROC_DT, YY.) LIKE '%2016%' 
GROUP BY PART_NO_ID

looks like it will return multiple observations since you are using a GROUP BY clause.

Shouldn't that subquery look more like

SELECT SUM(Total.PART_QTY) 
FROM DPO.DPO_PART_ORD_HST AS Total
WHERE  RSCCParts.PartID = STRIP(Total.PART_NO_ID)
  AND PUT(PROC_DT, YY.) LIKE '%2016%'

Your query has multiple references to RSCCPARTS table so you might need to introduce an alias to each so that you can clarify which one you want to use to get PARTID from to match to PART_NO_ID.

Thank you Tom. Your suggestions are spot on. I managed to do it by following your advice, especially about aliasing the main query and using it as a correlated subquery. — Haris Irshad, Mar 31 '17 at 00:29

SQL conditional for a field using multiple subqueries as cases

1 Answers1