CODEWITHSUNDEEP
sqlsql-serverms-access
Brian McCown
Brian McCown

Reputation: 1

Converting MS Access IIF to SQL Server CASE statement

I'm trying to convert a MS Access application to SQL Server. I'm getting an error

incorrect syntax near 'as'

when trying to convert this:

RegSales:

Sum(LOTOSMIS_ACC_TYPE.sing_ind*LOTOSMIS_RET_DAILY.grs_amn*IIf(gm_cd In (1105,2123,2124,2150,2152,2191,2192,5143,5145,5146,5245,5253),Switch(LOTOSMIS_RET_DAILY.gm_var=1,0,True,1),0))

To this

(SELECT CASE WHEN P.GM_VAR = 1 THEN 0 END)) AS RegSales,

I am getting error RegSales:

Sum(LOTOSMIS_ACC_TYPE.sing_ind*LOTOSMIS_RET_DAILY.grs_amn*IIf(gm_cd In (1105,2123,2124,2150,2152,2191,2192,5143,5145,5146,5245,5253),Switch(LOTOSMIS_RET_DAILY.gm_var=1,0,True,1),0))

Getting syntax errors

Incorrect syntax near the word 'as'

or

Incorrect syntax near ')'

What am I doing wrong?

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

If I follow correctly, the logic is:

sum(case when gm_cd not in (1105, 2123, 2124, 2150, 2152, 2191, 2192, 5143, 5145, 5146, 5245, 5253)
         then LOTOSMIS_ACC_TYPE.sing_ind * LOTOSMIS_RET_DAILY.grs_amn
         else 0
    end)

Upvotes: 3

Related Questions