Control Flow Issues ("CASE") 1

[Glowball]

I'm not sure how to do what I need to do, here. Basically, I need to do a query that results in a number, then based on that number I need to run another query that returns a recordset. In a nutshell:

SELECT CASE 
WHEN (SELECT MAX(LENGTH(a)) FROM A)>0 
THEN (SELECT b,c FROM B ORDER BY b) 
ELSE (SELECT c,d FROM C ORDER BY c) 
END

The individual queries work fine, but they don't work when I put them together. Am I just going at this wrong? Thanks!

 

[TonyGroves]

You can't do that sort of thing. A CASE clause is an expression which returns a single value, not a record set.

You would need to use program code to run the first query, then use its result to select the second query. It might be possible to come up with a single-query solution, but I think it would be so complicated and slow it wouldn't be worth it. (r937 might prove me wrong though).

 

[Glowball]

That's what I finally ended up doing, but it still means two separate queries. Thanks for clarifying the CASE rules, I didn't know that.

If anyone knows how this could be done in one query I'm all ears!