Is it possible to put a select statement in a case statement?

Question

I am currently trying to use a select statement inside my select statement. So far that works great. But i want it to select something different depending on some conditions. My problem is putting the SQL statement subquery in the THEN part of the CASE statement not the WHEN part. So my example is here. So i want to place a select statement inside a CASE statement which is all wraped in a sub query. Also i am using SQL server 2008.

Here is what i had originally :

    SELECT DISTINCT
       WORK_ORDER.PART_ID,
       OPERATION.SEQUENCE_NO,
       OPERATION.RESOURCE_ID,
       OPERATION.SETUP_HRS,
       OPERATION.RUN_HRS,
       OPERATION.OPERATION_TYPE,
       OPERATION.RUN AS PCS_HR,
       REPLACE(CONVERT( VARCHAR(150), CONVERT(BINARY(150), OPERATION_BINARY.BITS)), CHAR(0), '') AS Specs_OPR,
       OPERATION.WORKORDER_BASE_ID,
       OPERATION.WORKORDER_LOT_ID,
       OPERATION.WORKORDER_SPLIT_ID,
       OPERATION.WORKORDER_SUB_ID,
       OPERATION.LOAD_SIZE_QTY,
       OPERATION.CALC_START_QTY,
       OPERATION.COMPLETED_QTY,
       (CEILING(OPERATION.CALC_START_QTY / OPERATION.LOAD_SIZE_QTY)) AS NUM_O_LOADS,
       ((OPERATION.CALC_START_QTY - OPERATION.COMPLETED_QTY) / OPERATION.LOAD_SIZE_QTY) AS LOADS_REMAINING,

       -- SubQuery

(
    SELECT DISTINCT
           descriptions = STUFF(
                               (
                                   SELECT ', '+CHAR(13)+PART.DESCRIPTION
                                   FROM dbo.PART
                                        JOIN REQUIREMENT ON PART.ID = REQUIREMENT.PART_ID
                                   WHERE REQUIREMENT.WORKORDER_BASE_ID = '026877'
                                         AND REQUIREMENT.OPERATION_SEQ_NO = '30'
                                   FOR XML PATH(''), TYPE
                               ).value('.[1]', 'nvarchar(max)'), 1, 2, '')
    FROM(REQUIREMENT R
         JOIN PART P ON R.PART_ID = P.ID)
) AS PART_DESCRIPTION

-- End of SubQuery

FROM OPERATION
     INNER JOIN WORK_ORDER ON OPERATION.WORKORDER_TYPE = WORK_ORDER.TYPE
                              AND OPERATION.WORKORDER_BASE_ID = WORK_ORDER.BASE_ID
                              AND OPERATION.WORKORDER_LOT_ID = WORK_ORDER.LOT_ID
                              AND OPERATION.WORKORDER_SPLIT_ID = WORK_ORDER.SPLIT_ID
                              AND OPERATION.WORKORDER_SUB_ID = WORK_ORDER.SUB_ID
     INNER JOIN OPERATION_BINARY ON OPERATION.WORKORDER_TYPE = OPERATION_BINARY.WORKORDER_TYPE
                                    AND OPERATION.WORKORDER_BASE_ID = OPERATION_BINARY.WORKORDER_BASE_ID
                                    AND OPERATION.WORKORDER_LOT_ID = OPERATION_BINARY.WORKORDER_LOT_ID
                                    AND OPERATION.WORKORDER_SPLIT_ID = OPERATION_BINARY.WORKORDER_SPLIT_ID
                                    AND OPERATION.WORKORDER_SUB_ID = OPERATION_BINARY.WORKORDER_SUB_ID
                                    AND OPERATION.SEQUENCE_NO = OPERATION_BINARY.SEQUENCE_NO
     JOIN REQUIREMENT ON REQUIREMENT.WORKORDER_BASE_ID = OPERATION.WORKORDER_BASE_ID
WHERE(REPLACE(CONVERT(VARCHAR(8000), CONVERT(BINARY(8000), OPERATION_BINARY.BITS)), CHAR(0), '') NOT LIKE '%Gupta%')
     AND OPERATION.WORKORDER_BASE_ID = '026877'
     AND WORK_ORDER.BASE_ID = '026877';

This is what i have now but some reason my Case has created a 4th row to my 3 row result:

SELECT DISTINCT
       WORK_ORDER.PART_ID,
       OPERATION.SEQUENCE_NO,
       OPERATION.RESOURCE_ID,
       OPERATION.SETUP_HRS,
       OPERATION.RUN_HRS,
       OPERATION.OPERATION_TYPE,
       OPERATION.RUN AS PCS_HR,
       REPLACE(CONVERT( VARCHAR(150), CONVERT(BINARY(150), OPERATION_BINARY.BITS)), CHAR(0), '') AS Specs_OPR,
       OPERATION.WORKORDER_BASE_ID,
       OPERATION.WORKORDER_LOT_ID,
       OPERATION.WORKORDER_SPLIT_ID,
       OPERATION.WORKORDER_SUB_ID,
       OPERATION.LOAD_SIZE_QTY,
       OPERATION.CALC_START_QTY,
       OPERATION.COMPLETED_QTY,
       (CEILING(OPERATION.CALC_START_QTY / OPERATION.LOAD_SIZE_QTY)) AS NUM_O_LOADS,
       ((OPERATION.CALC_START_QTY - OPERATION.COMPLETED_QTY) / OPERATION.LOAD_SIZE_QTY) AS LOADS_REMAINING,

       -- SubQuery Inside Case

       CASE
           WHEN REQUIREMENT.OPERATION_SEQ_NO = '10'
                AND OPERATION.SEQUENCE_NO = '10'
           THEN
(
    SELECT DISTINCT
           descriptions = STUFF(
                               (
                                   SELECT ', '+CHAR(13)+PART.DESCRIPTION
                                   FROM dbo.PART
                                        JOIN REQUIREMENT ON PART.ID = REQUIREMENT.PART_ID
                                   WHERE REQUIREMENT.WORKORDER_BASE_ID = '026877'
                                         AND REQUIREMENT.OPERATION_SEQ_NO = '10'
                                   FOR XML PATH(''), TYPE
                               ).value('.[1]', 'nvarchar(max)'), 1, 2, '')
    FROM(REQUIREMENT R
         JOIN PART P ON R.PART_ID = P.ID)
)
       END AS DESCRIPTION

-- End of Subquery

FROM OPERATION
     INNER JOIN WORK_ORDER ON OPERATION.WORKORDER_TYPE = WORK_ORDER.TYPE
                              AND OPERATION.WORKORDER_BASE_ID = WORK_ORDER.BASE_ID
                              AND OPERATION.WORKORDER_LOT_ID = WORK_ORDER.LOT_ID
                              AND OPERATION.WORKORDER_SPLIT_ID = WORK_ORDER.SPLIT_ID
                              AND OPERATION.WORKORDER_SUB_ID = WORK_ORDER.SUB_ID
     INNER JOIN OPERATION_BINARY ON OPERATION.WORKORDER_TYPE = OPERATION_BINARY.WORKORDER_TYPE
                                    AND OPERATION.WORKORDER_BASE_ID = OPERATION_BINARY.WORKORDER_BASE_ID
                                    AND OPERATION.WORKORDER_LOT_ID = OPERATION_BINARY.WORKORDER_LOT_ID
                                    AND OPERATION.WORKORDER_SPLIT_ID = OPERATION_BINARY.WORKORDER_SPLIT_ID
                                    AND OPERATION.WORKORDER_SUB_ID = OPERATION_BINARY.WORKORDER_SUB_ID
                                    AND OPERATION.SEQUENCE_NO = OPERATION_BINARY.SEQUENCE_NO
     JOIN REQUIREMENT ON REQUIREMENT.WORKORDER_BASE_ID = OPERATION.WORKORDER_BASE_ID
WHERE(REPLACE(CONVERT(VARCHAR(8000), CONVERT(BINARY(8000), OPERATION_BINARY.BITS)), CHAR(0), '') NOT LIKE '%Gupta%')
     AND OPERATION.WORKORDER_BASE_ID = '026877'
     AND WORK_ORDER.BASE_ID = '026877';

Bottom line, No, you can't, but if you paste the SQL statement into the question I'm sure we can suggest an alternative approach that will accomplish what you are after. — Charles Bretana, Sep 08 '16 at 15:31
Ok sorry, i just thought it would be cleaner if i posted a imgur link since copy pasting can be quite messy and cumbersome, especially with the length of my query. — Azuraith, Sep 08 '16 at 15:53
Possible duplicate of [How to write a select inside case statement](http://stackoverflow.com/questions/25767993/how-to-write-a-select-inside-case-statement) — Shiva, Sep 08 '16 at 17:07
That is a bit different, i want the select statement in the THEN part of the case. not the when. — Azuraith, Sep 08 '16 at 17:21
are you getting any error by putting a select statement in the Then part of the case? — Kashif Qureshi, Sep 08 '16 at 18:52
No, the problem is that its creating a 4th row. one row I expected which adds the information of the subquery to the proper row based on the case. But then another row exists identical to that one but contains a NULL value where the subquery columns value is on the other one. — Azuraith, Sep 08 '16 at 19:09

score 3 · Accepted Answer · answered Sep 08 '16 at 20:48

I have found the solution to my specific problem. it was just my where clause causing added rows.

But to answer the original question of can you put a subquery select statement in a CASE. YES you can do it.

CASE
   WHEN condition
   THEN (select column1, from tbl join tbl2 on tbl1.column = tbl2.column)

   ELSE 
    (whatever)

END

Is it possible to put a select statement in a case statement?

1 Answers1