CODEWITHSUNDEEP
sqlsql-serverms-access
user2119980
user2119980

Reputation: 509

Converting CASE WHEN into IIF

I mainly work with SQL Server, and rarely use Access. I have case statement in SQL server that I need to turn into a nested IIF statement in Access and I am having a hard time getting it to work. The SQL Code is:

(CASE 
    WHEN (RRDD = '2029' 
        THEN 'IS' 
    WHEN RRDD = '2214' OR '2219' OR '2220' OR '2221' OR '2230' OR '2265'
        THEN 'AIR' 
    WHEN RRDD = '2044' OR '2323' OR '2327' OR '2331' OR '2339' 
        THEN 'LogDist' 
    WHEN RRDD = '2037' 
        THEN 'MailInn' 
    WHEN RRDD = '2213' OR '2307' OR '2311' OR '2332' OR '2334' OR '2338' 
             OR '2705' OR '2706'
        THEN 'GFF' 
    WHEN RRDD = '2010' 
        THEN 'Corp' 
    WHEN RRDD = '2040' OR '2041' OR '2081' OR '2086' 
        THEN 'Cap' 
      ELSE NULL 
END) AS RegDIs

Upvotes: 1

Views: 220

Answers (3)

HansUp
HansUp

Reputation: 97131

Consider Switch as an alternative to multiple IIf expressions.

Switch
    (
        RRDD = '2029', 'IS',
        RRDD IN ('2214','2219','2220','2221','2230','2265'), 'AIR',
        RRDD IN ('2044','2323','2327','2331','2339'), 'LogDist',
        RRDD = '2037', 'MailInn',
        RRDD IN ('2213','2307','2311','2332','2334','2338','2705','2706'), 'GFF',
        RRDD = '2010', 'Corp',
        RRDD IN ('2040','2041','2081','2086'), 'Cap'
    ) AS RegDIs

With Switch, when none of the condition match, the function returns Null.

I find Switch easier to understand especially when the number of IIfs is as large as you need for this.

Still, either the Switch or IIf approach amounts to writing data into the SQL statement. As others mentioned, I think a lookup table would be a better approach.

RRDD RegDIs
2029 IS
2214 AIR
2219 AIR
2220 AIR

It should be easier to edit the table when needed instead of revising a complex query.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Your original code is incorrect. You cannot say RRDD = '2044' OR '2323'. You can say: RRDD = '2044' OR RRDD = '2323'. or, you can use the in.

The access function iif only has the "then" and "else" clause. So, you have to nest the calls.

This makes the code a bit more cumbersome. And, keeping track of the closing parentheses can be a nightmare. As I suggest in my comment, a small reference table would be a much more elegant solution.

Here is the code using iif:

  select iif(RRDD = '2029', 'IS',
             iif(RRDD in ('2214', '2219', '2220', '2221', '2230', '2265'), 'AIR',
                 iif(RRDD in ('2044', '2323', '2327', '2331', '2339'), 'LogDist',
                     iif(RRDD = '2037', 'MailInn',
                         iif(RRDD in ('2213', '2307', '2311', '2332', '2334', '2338', '2705', '2706'), 'GFF',
                             iif(RRDD = '2010', 'Corp',
                                 iif(RRDD in ('2040', '2041', '2081',  '2086'), 'Cap', NULL
                                    )
                                )
                            )
                        )
                     )
                 )
             ) AS RegDI)

Upvotes: 0

sgeddes
sgeddes

Reputation: 62851

This case statement is crazy -- consider moving into an external table. It actual won't run as is -- for example, you have an extra parentheses and are using OR incorrectly.

With that said, basically you need to replace WHEN with IIF( and THEN with comma and include your next IIF as the final paramater -- this should be close: 

(IIF(RRDD = '2029', 'IS',
    IIF(RRDD IN ('2214', '2219', '2220', '2221', '2230', '2265'), 'AIR',
    IIF(RRDD IN ('2044', '2323', '2327', '2331', '2339'), 'LogDist',
    IIF(RRDD = '2037', 'MailInn',
    IIF(RRDD IN ('2213', '2307', '2311', '2332', '2334', '2338', '2705', '2706'), 'GFF',
    IIF(RRDD = '2010', 'Corp',
    IIF(RRDD IN ('2040', '2041', '2081', '2086'), 'Cap',
    NULL)))))))) AS RegDIs

Upvotes: 3

Related Questions