Select from different tables via SQL depending on flag

Question

I have a script to extract certain data from a much bigger table, with one field in particular changing regularly, e.g.

SELECT CASE @Flag WHEN 1 THEN t.field1 WHEN 2 THEN t.field2 WHEN 3 
       THEN t.field3 END as field,
       ...[A bunch of other fields]
FROM table t

However, the issue is now I want to do other processing on the data. I'm trying to figure out the most effective method. I need to have some way of getting the flag through, so I know I'm talking about data sliced by the right field.

One possible solution I was playing around with a bit (mostly to see what would happen) is to dump the contents of the script into a table function which has the flag passed to it, and then use a SELECT query on the results of the function. I've managed to get it to work, but it's significantly slower than...

The obvious solution, and probably the most efficient use of processor cycles: to create a series of cache tables, one for each of the three flag values. However, the problem then is to find some way of extracting the data from the right cache table to perform the calculation. The obvious, though incorrect, response would be something like

SELECT CASE @Flag WHEN 1 THEN table1.field WHEN 2 THEN table2.field WHEN 3 
       THEN table3.field END as field,
       ...[The various calculated fields]
FROM table1, table2, table3

Unfortunately, as is obvious, this creates a massive cross join - which is not my intended result at all.

Does anyone know how to turn that cross join into an "Only look at x table"? (Without use of Dynamic SQL, which makes things hard to deal with?) Or an alternative solution, that's still reasonably speedy?

EDIT: Whether it's a good reason or not, the idea I was trying to implement was to not have three largely identical queries, that differ only by table - which would then have to be edited identically whenever a change is made to the logic. Which is why I've avoided the "Have the flag entirely separate" thing thus far...

Answer 1

I think you need to pull @Flag out of the query altogether, and use it to decide which of three separate SELECT statements to run.

Answer 2

You seem to be focusing your attention on the technology rather than the problem to be solved. Think about one select from the main table for each case - which is how you describe it here, isn't it?

Answer 3

How about a UNION ALL for each value of FLAG.

In the where clause of the first bit include:

AND @flag = 1

Although the comment about running different select statements for different flag values also makes sense to me.

Answer 4

A simpler solution, and one suggested by a workmate:

SELECT CASE @Flag WHEN 1 THEN t.field1 WHEN 2 THEN t.field2 WHEN 3 
   THEN t.field3 END as field,
   [A bunch of other fields],
   @Flag as flag
FROM table t

Then base the decision making on the last field. A lot simpler, and probably should have occurred to me in the first place.