SQL Case Statement on Result

Question

I have the following SQL Query:

SELECT IIF(clock_to - clock_at_from < 0,
           12 - (clock_at_from - clock_to),
           clock_to - clock_at_from) AS clock_pos
FROM Condition

Which outputs numbers from 1-10

I think I want to preform a CASE statement on clock_pos where

If clock_pos = 1 value = 3
If clock_pos = 2 value = 4
If clock_pos > 3 value = 5

To then add the final result back as a new column in the Conditions table...

Yet I'm unsure how to write this using the result named clock_pos or if there is a better way altogether.

@EDIT as per TONY Nested If attempt. I was unable to make an additional If statment

SELECT IIF(clock_to - clock_at_from < 0,
           IIF(12 - (clock_at_from - clock_to) >= 3, 5, 12 - (clock_at_from - clock_to)),
           clock_to - clock_at_from) as clock_pos
FROM Conditions

Use nested `if` statements. I don't believe there is a `CASE` statement in Access. — Tony, Jan 13 '16 at 20:06
@Tony I tried that and sorta worked but I could only write one, example above — Tristan Forward, Jan 13 '16 at 20:07
Use a Switch statement. https://support.office.com/en-us/article/Switch-Function-d750c10d-0c8e-444c-9e63-f47504f9e379?CorrelationId=fb10a569-3985-4651-99b3-44a6bccff767&ui=en-US&rs=en-US&ad=US — Fred, Jan 13 '16 at 20:11
An example http://stackoverflow.com/questions/15774078/what-is-the-equivalent-of-select-case-in-access-sql — Fred, Jan 13 '16 at 20:12
@Fred I attempted this but no luck. To be clear in need to run off the result of clock_pos and is not in the database. I need to calculate the values first then calculate another value based on those values. — Tristan Forward, Jan 13 '16 at 20:43

score 1 · Accepted Answer · answered Jan 13 '16 at 21:49

MS Access SQL actually allows use of calculated columns within same query:

SELECT IIF(clock_to - clock_at_from < 0,
           12 - (clock_at_from - clock_to),
           clock_to - clock_at_from) AS clock_pos,
       IIF(clock_pos = 1, 3,
           IIF(clock_pos = 2, 4,
               IIF(clock_pos > 3, 5, NULL))) As clock_pos_value
FROM Condition

Gordon Linoff · Answer 2 · 2016-01-14T02:12:59.237

0

It seems pretty simple to convert the CASE logic to nested IIF() if you use a subquery:

SELECT IIF(clock_pos = 1, 3
            IIF(clock_pos = 2, 4,
                IIF(clock_pos > 3, 5, NULL)
               )
          ) as [value]
FROM (SELECT IIF(clock_to - clock_at_from < 0,
                 IIF(12 - (clock_at_from - clock_to) >= 3,
                     5, 12 - (clock_at_from - clock_to)
                    ), clock_to - clock_at_from
                ) as clock_pos
      FROM Conditions
     ) as c

edited Jan 14 '16 at 02:12

answered Jan 13 '16 at 20:15

Gordon Linoff

1,242,037
58
646
786

Logic makes sense however when try to test get error: The SELECT statement includes a reserved work or an argument name is misspelled or missing. – Tristan Forward Jan 13 '16 at 20:27
That is `as Value` causing that. `Value` is a reserved word. Try `As [Value]` – Fred Jan 13 '16 at 21:00
@TristanForward . . . I was just following the names used in the question. Square braces are needed. – Gordon Linoff Jan 14 '16 at 02:13

SQL Case Statement on Result

2 Answers2