Translate IIF statement to CASE statement in SELECT clause. 2

[08211987]

Hi,
I have SQL from an access query that I need to translate to T-SQL that contains an IIF statement which needs to be changed to a CASE. Can someone tell what the syntax would look like for the below IIF statement within the SELECT statement?

SELECT DISTINCT LOWES_T2580_GIN_VBU.T2580_GIN_ID,
LOWES_T2580_GIN_VBU.ADD_DT,
LOWES_T2580_GIN_VBU.T617_FNC_TYP_CD,
LOWES_T2580_GIN_VBU.T1989_TGT_CRY_CD,
LOWES_T2580_GIN_VBU.T616_VBU_NBR, LOWES_T2580_GIN_VBU.T2582_GIN_STS_CD,
IIf([LOWES_T2580_GIN_VBU.MOD_IDF_TXT]<>" ", [LOWES_T2580_GIN_VBU.MOD_IDF_TXT], [LOWES_T2605_GIN_VBU_MRK1.SLL_ITM_IDF]) AS MOD_IDF_TXT, LOWES_T2593_GIN_CSM_UNT.SHT_DES_TXT AS [Vendor Description],
LOWES_T2580_GIN_VBU.BAS_UNT_IDC,
LOWES_T2580_GIN_VBU.CSM_UNT_IDC,
LOWES_T2580_GIN_VBU.LWS_CSM_UNT_IDC,
LOWES_T2584_PAK_HRC_TYP1.SHT_DES_TXT,
LOWES_T2593_GIN_CSM_UNT.WEB_PRD_IDC

Thanks!
C

 

[SkipVought]

Hi,

Case when LOWES_T2580_GIN_VBU.MOD_IDF_TXT]<>" " then
   [LOWES_T2580_GIN_VBU.MOD_IDF_TXT]
Else 
   [LOWES_T2605_GIN_VBU_MRK1.SLL_ITM_IDF]
End

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[08211987]

So I guess I am wondering, would the format be this below and the other fields will also process under the "SELECT"? Just put a comma after the statement? Or does it need to move lower in the code? The flow just doesn't seem to flow unless I am over thinking it before trying.
Appreciate your help, Skip.

SELECT DISTINCT LOWES_T2580_GIN_VBU.T2580_GIN_ID,
LOWES_T2580_GIN_VBU.ADD_DT,
LOWES_T2580_GIN_VBU.T617_FNC_TYP_CD,
LOWES_T2580_GIN_VBU.T1989_TGT_CRY_CD,
LOWES_T2580_GIN_VBU.T616_VBU_NBR, LOWES_T2580_GIN_VBU.T2582_GIN_STS_CD,
Case when LOWES_T2580_GIN_VBU.MOD_IDF_TXT]<>" " then
[LOWES_T2580_GIN_VBU.MOD_IDF_TXT]
Else
[LOWES_T2605_GIN_VBU_MRK1.SLL_ITM_IDF]
End,
LOWES_T2580_GIN_VBU.BAS_UNT_IDC,
LOWES_T2580_GIN_VBU.CSM_UNT_IDC,
LOWES_T2580_GIN_VBU.LWS_CSM_UNT_IDC,
LOWES_T2584_PAK_HRC_TYP1.SHT_DES_TXT,
LOWES_T2593_GIN_CSM_UNT.WEB_PRD_IDC

 

[dhookom]

Skip had some typos in his expression. There shouldn't be a need for any []s. It doesn't make any difference where the CASE WHEN appears within the SELECT clause.

SELECT DISTINCT LOWES_T2580_GIN_VBU.T2580_GIN_ID, 
LOWES_T2580_GIN_VBU.ADD_DT, 
LOWES_T2580_GIN_VBU.T617_FNC_TYP_CD, 
LOWES_T2580_GIN_VBU.T1989_TGT_CRY_CD, 
LOWES_T2580_GIN_VBU.T616_VBU_NBR, LOWES_T2580_GIN_VBU.T2582_GIN_STS_CD, 
Case when LOWES_T2580_GIN_VBU.MOD_IDF_TXT<>" " then
LOWES_T2580_GIN_VBU.MOD_IDF_TXT
Else 
 LOWES_T2605_GIN_VBU_MRK1.SLL_ITM_IDF
End  AS MOD_IDF_TXT,  
LOWES_T2580_GIN_VBU.BAS_UNT_IDC, 
LOWES_T2580_GIN_VBU.CSM_UNT_IDC, 
LOWES_T2580_GIN_VBU.LWS_CSM_UNT_IDC, 
LOWES_T2584_PAK_HRC_TYP1.SHT_DES_TXT, 
LOWES_T2593_GIN_CSM_UNT.WEB_PRD_IDC

Duane
Hook'D on Access
MS Access MVP

 

[08211987]

Thanks both of you for responding so quickly and for helping me learn something new!

 

[dhookom]

I'm a little perplexed by

LOWES_T2580_GIN_VBU.MOD_IDF_TXT<>" "

since Access doesn't typically allow storing of a single space. I would think filtering against something you can't see is a bit confusing.

Duane
Hook'D on Access
MS Access MVP