Combining CASE STATEMENTS

Question

Possible Duplicate:
Converting Access Code to SQL

I have an access code using IIF statements that have 2 in 1 and I am trying to rewrite them for SQL as CASE statements but I am hitting some speedbumps.

Sum(IIf([RRDD] Not Like "12*" 
    And [RRDD] Not Like "13*" 
    And [RRDD] Not Like "16*" 
    And [RRDD] Not Like "17*" 
    And [RRDD] Not Like "2706" 
    And [RRDD] Not Like "2707" 
    And [RRDD] Not Like "2331",
    CDbl([2011 Total])*IIf(IsNumeric([Dur_In_Hours]),
    CDbl([Dur_In_Hours]),0),0)) AS SP_DOM_COST

WHere the first IIF is being multiplied by the second IIF is where I am confused, and i need it to be in SQL format

There's nothing wrong with multiplying `(CASE ... WHEN ... END)` by another `(CASE ... WHEN ... END)`, so what's the problem? — Anton Kovalenko, Jan 21 '13 at 20:45
I dont know how to format the code in SQL version, IE the second CASE WHEN portion — user1958651, Jan 21 '13 at 21:04

score 2 · Answer 1 · answered Jan 21 '13 at 21:59

You can simplify the logic in both SQL Server and Access. Here is the SQL Server version:

Sum(case when left([RRDD], 2) not in ('12', '13', '16', '17') and
              RRDD not in ('2706', '2707', '2331') and
              isnumeric(dur_in_hours) = 1
         then [2011 Total] * cast(Dur_In_Hours as float)
         else 0.0
    end) AS SP_DOM_COST

In both systems, like is going to incur more overhead than a search through a list of constants. The cast is safe because it is in a where clause (I'm also adding an answer to the reference above, since the answers are not correct).

The replacement for iif is generally case, although this function has been added into SQL Server 2012 (that said, I sort of hope no one uses it).

score 1 · Answer 2 · edited May 23 '17 at 12:12

Sum(case when [RRDD] Not Like '12%' 
And [RRDD] Not Like '13%' 
And [RRDD] Not Like '16%' 
And [RRDD] Not Like '17%' 
And [RRDD] Not Like '2706' 
And [RRDD] Not Like '2707' 
And [RRDD] Not Like '2331'
then cast([2011 Total] as float) * 
    case when ISNUMERIC([Dur_In_Hours]) = 1
        then cast([Dur_In_Hours] as float) 
        else 0 
    end
else 0 
end) AS SP_DOM_COST

While IsNUmeric is a valid TSQL function it is apparently not very nice to play with. IF you get odd results check out some other existing answers for workaround. Depending on your data you may be fine though.

Not sure but perhaps `*`s should be changed to `%`s. – Andriy M Jan 21 '13 at 21:36 — Andriy M, Jan 21 '13 at 21:36

Combining CASE STATEMENTS

2 Answers2