change conditions inside WHERE clause depending on the values

October 16, 2023

I have this WHERE clause inside my sql query:

WHERE ta.year = 2023 and (0=25 or dep1.code = 25) and (0=0 or d.code = 0) and (0=0 or sg.group_code = 0) and (0=0 or sf.code = 0)

So I need to change one of the conditions depending on the parameters I get. Its (0=25 or dep1.code = 25)

If the request accepts parameters 25, 30 or 50, I need to use (0=25 or dep1.code = 25)
else (0=10 or a.branch= 10)

Its like i need something like this:

If (parameter == 25) 
{
where clause = (0=25 or dep1.code = 25)
}
else where clause = (0=10 or a.branch= 10)

I need the solution inside sql query because im using birt

>Solution :

Use AND and OR:

WHERE (   parameter = 25
      AND (0 = 25 OR dep1.code = 25))
OR    (   (parameter != 25 OR parameter IS NULL)
      AND (0 = 10 OR a.branch = 10))

However, 0 = 25 and 0 = 10 will never be true so you can simplify it to:

WHERE (   parameter = 25
      AND dep1.code = 25)
OR    (   (parameter != 25 OR parameter IS NULL)
      AND a.branch = 10)

Which for your entire WHERE clause would be:

WHERE ta.year = 2023
AND   (  (   parameter = 25
         AND (0 = 25 OR dep1.code = 25))
      OR (   (parameter != 25 OR parameter IS NULL)
         AND (0 = 10 OR a.branch = 10))
      )
AND   (0=0 OR d.code = 0)
AND   (0=0 OR sg.group_code = 0)
AND   (0=0 OR sf.code = 0)

Which can be simplified to:

WHERE ta.year = 2023
AND   (  (   parameter = 25
         AND dep1.code = 25)
      OR (   (parameter != 25 OR parameter IS NULL)
         AND a.branch = 10)
      )