SQL Server return a list in a select case in where statement

user_1766419 0
Viewed 59

I have the following query:

SELECT  cpa_0.[Period] as "Period", cpa_0.[Account] as "Account", cpa_0.[Center] as "Center", cpa_0.[Scenario] as "Scenario", cpa_0.[Effective_Date] as "Effective_Date" 
     FROM [dbo].[GLDetail] cpa_0 
     WHERE cpa_0.[Center] =  $$Centers-VALUE$$ and
           cpa_0.[Account] = $$Accounts-VALUE$$ and
           cpa_0.[Period] = case  
           when $$View-VALUE$$ = 'Periodic' then $$Periods-VALUE$$
           when $$View-VALUE$$ = 'Y_T_D' then ('2017-09','2017-08')
           end

The VALUE$$ are variables passes and they work fine. My issue is on the last line of the query when $$View-VALUE$$ = 'Y_T_D' then ('2017-09','2017-08'), this does nto work. I need to return a list when my var equals Y_T_D, how can i do that?

Regards

list sql-server sql
2 Answers

A WHERE clause is a boolean expression. There is no need to additionally create boolean expressions here with CASE WHEN; simply use AND and OR.

SELECT 
  Period,
  Account
  Center,
  Scenario,
  Effective_Date
FROM dbo.GLDetail  
WHERE Center =  $$Centers-VALUE$$ 
  AND Account = $$Accounts-VALUE$$ 
  AND 
  (
    ($$View-VALUE$$ = 'Periodic' AND Period = $$Periods-VALUE$$)
     OR
    ($$View-VALUE$$ = 'Y_T_D' AND Period IN ('2017-09', '2017-08'))
  );
user_22707620

you can change where clause like this:

SELECT  cpa_0.[Period] as "Period", cpa_0.[Account] as "Account", cpa_0.[Center] as "Center", cpa_0.[Scenario] as "Scenario", cpa_0.[Effective_Date] as "Effective_Date" 
 FROM [dbo].[GLDetail] cpa_0 
 WHERE cpa_0.[Center] =  $$Centers-VALUE$$ and
       cpa_0.[Account] = $$Accounts-VALUE$$ and
       case  
       when $$View-VALUE$$ = 'Periodic' AND cpa_0.[Period] =  $$Periods-VALUE$$ THEN 1
       when $$View-VALUE$$ = 'Y_T_D' AND cpa_0.[Period] IN ('2017-09','2017-08') THEN 1
       ELSE 0
       END = 1
user_85762730
Related