CASE statement in FROM clause

Question

Can you use CASE in the FROM clause of a SELECT statement to determine from which table to retrieve data?

My database has multiple versions of a table. The value of an input parameter in a procedure will tell the procedure whether to retrieve data from version 1, 2 or 3. The syntax I am trying to use is similar to:

SELECT * FROM (CASE input_parameter WHEN 1 THEN version1 WHEN 2 THEN version 2 WHEN 3 THEN version3 END) WHERE ...

Can this be done? If so, am I using the correct syntax?

possible duplicate of [how to set table name in dynamic sql query?](http://stackoverflow.com/questions/20678725/how-to-set-table-name-in-dynamic-sql-query) — Allan, Nov 14 '14 at 19:01

score 0 · Answer 1 · answered Nov 14 '14 at 19:00

0

It can't be done in the SQL statement itself. You'll need to construct the SQL statement dynamically in order to achieve this kind of result.

answered Nov 14 '14 at 19:00

Allan

17,141
4
52
69

score 0 · Answer 2 · answered Nov 14 '14 at 19:13

You can't dynamically select the table like that in straight-up SQL. You would need a stored procedure to do exactly what you are wanting. There are some workarounds though.

You could do something janky in your FROM clause like:

 SELECT *
 FROM 
    (SELECT null as "whatever") as fakeTable
    LEFT OUTER JOIN version1 on input_parameter = 1
    LEFT OUTER JOIN version2 on input_parameter = 2
    LEFT OUTER JOIN version3 on input_parameter = 3

This will work since the input_parameter can only be one value at a time. Should you decide you want both version1 and version2 joined if the input_parameter is 2 then you will end up with a cross join and may god have mercy on your soul.

You could do something janky with a UNION:

SELECT * FROM version1 WHERE input_paramter=1
UNION ALL
SELECT * FROM version2 WHERE input_paramter=2
UNION ALL
SELECT * FROM version3 WHERE input_paramter=3

This is a bit nicer since a screw up will only bring back 2 or 3 times as many results instead of the screw up in example 1 where you get n^2 or n^3 results.

I'm not sure which one is going to cause more trouble from a CPU-I/O standpoint, but I would guess that they are pretty close from an execution path standpoint, and if the data is small, it probably won't matter anyway.

CASE statement in FROM clause

2 Answers2