Case statement in a SELECT--FROM

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

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