CASE Statement Assistance

Transact-SQL
1

Hello Masters,

I need to create a statement that will output a list of business sub classes base on if the business is a Large "L", Small "S", WomanOwned "F", or "N"

The column " V.S_CL_SM_BUS_CD " holds this value. Here is the basic query I have -

SELECT V.S_CL_SM_BUS_CD
,V.VEND_NAME_EXT
,LNHS.CST_AMT
,VCHR.VEND_ID
,V.CL_VET_FL
,V.CL_SD_VET_FL
,V.CL_ANC_IT_FL
,V.CL_DISADV_FL
,V.CL_WOM_OWN_FL
,V.CL_LAB_SRPL_FL
,V.CL_HIST_BL_CLG_FL
,V.CL_ANC_IT_FL
,VCHR.VCHR_KEY

FROM WEBAPP_CP.DELTEK.V_VEND v
LEFT JOIN WEBAPP_CP.DELTEK.V_VCHR_HDR_HS VCHR
ON v.VEND_ID = VCHR.VEND_ID
JOIN WEBAPP_CP.DELTEK.VCHR_LN_ACCT_HS LNHS
ON VCHR.VCHR_KEY = LNHS.VCHR_KEY

So for example if V.S_CL_SM_BUS_CD is "F", then I want to show data if any of these sub categories are "Y" -

V.CL_VET_FL
V.CL_SD_VET_FL
V.CL_ANC_IT_FL
V.CL_DISADV_FL
V.CL_WOM_OWN_FL
V.CL_LAB_SRPL_FL
V.CL_HIST_BL_CLG_FL
V.CL_ANC_IT_FL

2

you mean:

CASE V.S_CL_SM_BUS_CD
     WHEN 'L' THEN 'LARGE'
     WHEN 'S' THEN 'SMALL'
     ... etc.
END as BusinessSubClass

?

3

Thanks for the response. Yes, but I want to create a condition when it is a "L", "S", ect...business, then look at these columns and check if they are "Y"

V.CL_VET_FL
V.CL_SD_VET_FL
V.CL_ANC_IT_FL
V.CL_DISADV_FL
V.CL_WOM_OWN_FL
V.CL_LAB_SRPL_FL
V.CL_HIST_BL_CLG_FL
V.CL_ANC_IT_FL

If they are "Y", then show them as well as well the other columns

VCHR.VCHR_KEY
,V.VEND_NAME_EXT
,LNHS.CST_AMT
,VCHR.VEND_ID

Hope this makes sense. Let me know if you need more clarity. Thanks as always.

4

You cannot return a variable number of columns from a single query. So there's no way to show some columns then

If they are "Y", then show them as well as well the other columns

5

Understood. So let's take this approach then. Can we case V.S_CL_SM_BUS_CD for each business class to show data for all the columns. This time not returning a variable number of columns, but just taking, for example, "L" and show the results of all the columns?

Thanks for your quick support.

6

OK -- so you can make an expression concatenating those columns if you get the S_CL_SM_BUS_CD you want, defaulting to NULL. Use a CASE statement for that

7

Would you be able to provide a small line or method to get me started on the rest of this query? I appreciate all your help.

MC

8 
select case when V.CL_VET_FL = 'Y'
             and V.CL_SD_VET_FL = 'Y'
             and ...
            then VCHR.VCHR_KEY 
               + V.VEND_NAME_EXT 
               + LNHS.CST_AMT
               + VCHR.VEND_ID
      end as 'other columns'