CODEWITHSUNDEEP
sqlms-access-2010
Olli
Olli

Reputation: 676

ms-access-2010 returns #func[exclamation mark]

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?

Upvotes: 0

Views: 47

Answers (1)

Erik A
Erik A

Reputation: 32642

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)

Upvotes: 1

Related Questions