I have a sql-statement which includes a switch, i want to execute on ms-access-2010.
sql:
SELECT TABLE_1.ID, SWITCH ( (TABLE_3.ID IN (7,8) AND TABLE_1.SOME_COLUMN = 2), (SELECT MAX(TABLE_4.ID) FROM TABLE_4 WHERE TABLE_4.TABLE_2_ID = TABLE_2.ID), (SELECT MIN(TABLE_4.SOME_COLUMN) FROM TABLE_4 WHERE TABLE_4.TBL_2_ID = TABLE_1.TBL_2_ID) )AS SOME_ALIAS FROM ((TABLE_1 LEFT JOIN TABLE_2 ON TABLE_1.TABLE_2_ID = TABLE_2.ID) LEFT JOIN TABLE_3 ON TABLE_2.TABLE_3_ID = TABLE_3.ID)
In my result, the column ‘some_column’ is always #Function!
If i do not use the switch-statement and add the sub-selects from the two cases as normal selected-fields like this:
SELECT TABLE_1.ID, (SELECT MAX(TABLE_4.ID) FROM TABLE_4 WHERE TABLE_4.TABLE_2_ID = TABLE_2.ID) AS SOME_COLUMN_1, (SELECT MIN(TABLE_4.SOME_COLUMN) FROM TABLE_4 WHERE TABLE_4.TBL_2_ID = TABLE_1.TBL_2_ID) AS SOME_COLUMN_2 FROM ((TABLE_1 LEFT JOIN TABLE_2 ON TABLE_1.TABLE_2_ID = TABLE_2.ID) LEFT JOIN TABLE_3 ON TABLE_2.TABLE_3_ID = TABLE_3.ID)
i don’t get any problems (There are only a couple of rows where there is no value inside of SOME_COLUMN_1 AND SOME_COLUMN_2).
What is causing the result of #FUNCTION!
here?
Advertisement
Answer
The Switch
statement requires an even number of arguments. You’re only passing 3 arguments, thus causing an error.
You can either use IIF
, or add an additional comparison argument (Perhaps just True
as 3rd argument if you want to return the last argument if the first one isn’t true).
SELECT TABLE_1.ID, IIF ( (TABLE_3.ID IN (7,8) AND TABLE_1.SOME_COLUMN = 2), (SELECT MAX(TABLE_4.ID) FROM TABLE_4 WHERE TABLE_4.TABLE_2_ID = TABLE_2.ID), (SELECT MIN(TABLE_4.SOME_COLUMN) FROM TABLE_4 WHERE TABLE_4.TBL_2_ID = TABLE_1.TBL_2_ID) )AS SOME_ALIAS FROM ((TABLE_1 LEFT JOIN TABLE_2 ON TABLE_1.TABLE_2_ID = TABLE_2.ID) LEFT JOIN TABLE_3 ON TABLE_2.TABLE_3_ID = TABLE_3.ID)