Case statement in a SELECT--FROM

9674 Views

All,

I have a need to call different tables depending on the Input Parameter, so can I make a select statement and in the FROM can I use a Case-When-End as below?

This statement works

SELECT ID, SHORT_NAME, LONG_NAME, sum(KOUNT) as KOUNT

FROM

(

select ID, SHORT_NAME, LONG_NAME, 1 as KOUNT

from "CMS"."SITE" WHERE SHORT_NAME = :IP_SITE_CODE

)

GROUP BY ID, SHORT_NAME, LONG_NAME;

But if I put the following I get a syntax error near FROM is what I get.

SELECT ID, SHORT_NAME, LONG_NAME, sum(KOUNT) as KOUNT

FROM

( case when :SITE_CODE = 'GRV'

select ID, SHORT_NAME, LONG_NAME, 1 as KOUNT

from SHOPS WHERE SHORT_NAME = :IP_SHOP

else

select 0 as ID, 'NA' as SHORT_NAME, 'NA' as LONG_NAME from DUMMY

end

)

GROUP BY ID, SHORT_NAME, LONG_NAME;

Can this be done? or any other option of doing it in SQL?

Thanks,

Arthur.