CODEWITHSUNDEEP
sqloracle-database
user5602591
user5602591

Reputation:

Switch case in where clause oracle

I have a below scenario. Could you please correct the below query..

 where l.au=m.acad_group

 and l.acad_prog = m.acad_prog

 case when acad_plan is not null then and l.acad_plan = m.acad_plan else and 0=0 
 end case

case when l.acad_sub_plan is not null then and l.acad_sub_plan=m.acad_sub_plan 
else and 0=0 end case;

Upvotes: 1

Views: 1253

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

Don't use CASE WHEN in the WHERE clause. Use AND and OR and parentheses.

where l.au = m.acad_group
  and l.acad_prog = m.acad_prog
  and (and l.acad_plan = m.acad_plan or l.acad_plan is null)
  and (and l.acad_sub_plan = m.acad_sub_plan or l.acad_sub_plan is null)

Upvotes: 1

D-Shih
D-Shih

Reputation: 46239

If I understand correct,You can use OR and AND instead of CASE WHEN

you can try this.

I have a little question on acad_plan is null, which acad_plan did you want to check l.acad_plan or m.acad_plan?

where l.au=m.acad_group 
AND l.acad_prog = m.acad_prog
AND (acad_plan is null  OR( acad_plan is not null AND l.acad_plan = m.acad_plan))
AND (l.acad_sub_plan is null  OR( l.acad_sub_plan  is not null AND l.acad_sub_plan=m.acad_sub_plan))

Upvotes: 1

Related Questions