CODEWITHSUNDEEP
sqloracle-databasecasewhere-clause
Rob Morris
Rob Morris

Reputation: 137

CASE statement in WHERE clause to look for multiple values using IN

I'm looking to write a SQL statement to report on different rounds depending on the current day of the week.

So if the current day is Monday, I want the report to show me information on rounds 'NOMN','EWM1','EWM2' and 'SOMN' - but if the current day is Tuesday I want the report to show me rounds 'NOTE','EWT1','EWT2','SOTE' and so on for Weds, Thurs, Fri.

Current Code is as follows

   SELECT
   insp_route_feat.insp_route_code,
   insp_route_feat.site_code,
   insp_route_feat.plot_number,
   insp_route_feat.officer_code,
   max (inspection_feature.feature_insp_date) as Last_Inspection_Date,

   case
   when  max (inspection_feature.feature_insp_date) >=trunc(SYSDATE,'DD') then 'Inspected'
   else 'Not Completed' end as Assigned


   FROM
   insp_route_feat
   inner join inspection_feature on insp_route_feat.site_code = inspection_feature.site_code and 
   insp_route_feat.plot_number = inspection_feature.plot_number


   WHERE  insp_route_feat.insp_route_code = 
   (CASE WHEN to_char(sysdate,'DY') = 'MON' THEN 'NOMN'
           WHEN to_char(sysdate,'D') = 'TUE' THEN 'NOTE'
                        ELSE null END)

   group by
   insp_route_feat.insp_route_code,
   insp_route_feat.site_code,
   insp_route_feat.plot_number,
   insp_route_feat.officer_code

What I would like the WHERE statement to say is something like

WHERE  insp_route_feat.insp_route_code IN ( 
   (CASE WHEN to_char(sysdate,'DY') = 'MON' THEN 'NOMN','EWM1','EWM2','SOMN'
           WHEN to_char(sysdate,'D') = 'TUE' THEN 'NOTE','EWT2','EWT2','SOTE'
                        ELSE null END)

however I know that coding is wrong, but that hopefully gives an idea of what I'm after.

Upvotes: 0

Views: 951

Answers (3)

Popeye
Popeye

Reputation: 35900

Yes, boolean expreasion makes it easier but if you want case when statement in where clause then there is an option as follows:

CASE
  WHEN to_char(sysdate, 'DY') = 'MON' 
       and insp_route_feat.insp_route_code IN ('NOMN', 'EWM1', 'EWM2', 'SOMN') 
  THEN 1
  WHEN to_char(sysdate, 'DY') = 'TUE' 
       and insp_route_feat.insp_route_code IN ('NOTE', 'EWT2', 'EWT2', 'SOTE')
  THEN 1
END = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Just use boolean expressions:

WHERE ( to_char(sysdate, 'DY') = 'MON' and 
        insp_route_feat.insp_route_code IN ('NOMN', 'EWM1', 'EWM2', 'SOMN')
      ) OR
      ( to_char(sysdate, 'DY') = 'TUE' and 
        insp_route_feat.insp_route_code IN ('NOTE', 'EWT2', 'EWT2', 'SOTE')
      )

CASE expressions are rarely needed in WHERE clauses. Note that I also fixed the second condition to use 'DY' instead of 'D'.

Upvotes: 1

GMB
GMB

Reputation: 222432

Boolean logic makes it far easier than a case expression to phrase such logic:

WHERE (to_char(sysdate,'DY') = 'MON' and insp_route_feat.insp_route_code IN ('NOMN','EWM1','EWM2','SOMN'))
   OR (to_char(sysdate,'DY') = 'TUE' and insp_route_feat.insp_route_code IN ('NOTE','EWT2','EWT2','SOTE'))

Upvotes: 1

Related Questions