Question Re: Case statement in a SELECT--FROM in Technology Q&A

Case statement in a SELECT--FROM

Former Member — Tue, 20 Sep 2016 15:56:55 GMT

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.

Re: Case statement in a SELECT--FROM

Former Member — Tue, 20 Sep 2016 16:37:28 GMT

Hi Arthur,

You can simply write below SQL in Scripted Calculation view where IP_SITE_CODE is Input Parameter:

SELECT ID, SHORT_NAME

FROM

( select ID, (CASE WHEN "SHORT_NAME" = :IP_SITE_CODE THEN "SHORT_NAME" ELSE 'N/A' END) AS "SHORT_NAME" from SHOPS

)

I have taken only 2 fields for explanation. Similar logic you can add for ID and other fields too.

Hope this helps.

Regards

Randhir Jha

Re: Case statement in a SELECT--FROM

Former Member — Tue, 20 Sep 2016 16:59:16 GMT

Hi Arthur,

Missed to mention that you can add both the condition in Case statement. Here is the revised SQL

SELECT ID, SHORT_NAME

FROM

( select ID, (CASE WHEN "SHORT_NAME" = :IP_SITE_CODE and "SITE_CODE" = 'GRV' THEN "SHORT_NAME" ELSE 'N/A' END) AS "SHORT_NAME" from SHOPS

)

Regards

Randhir Jha

Re: Case statement in a SELECT--FROM

Former Member — Tue, 20 Sep 2016 18:08:21 GMT

Randhir,

That is a good sql, but what I am looking for is two different tables selected based on the input. That is why I have SHOP and DUMMY in my original example. The issue is I have same columns come from different tables but which table should I select from will be decided based on my INPUT.

I could UNION all the tables since they all have same column names, but that is exactly what I want to avoid by using case with in the SQL.

Something like below

select col1, col2

from (

(case when SITE = 'TX' then select col1, col2 from TX-TABLE else

( case when SITE = 'LA' then select col1, col2 from LA-TABLE else

select col1, col2 from OTHER_TABLE

end

)

end

)

Is it possible?

Thanks,

Arthur.

Re: Case statement in a SELECT--FROM

Former Member — Tue, 20 Sep 2016 21:37:45 GMT

Arthur,

You can do this using same SQL with additional condition.Where condition is required to ensure that Select returns only one row

select col1, col2

from (

(case when SITE = 'TX' then select distinct col1 from TX-TABLE where Condition

when SITE = 'LA' then select distinct col1 from LA-TABLE where Condition

else 'None' end) as Col1,

Repeat same case statement for each column.

Regards

Randhir Jha