This is a tip I inspired today as I have to make a cursor and needed to use a dynamic where in order to avoid COBOL code.
It is well known to the SQL programmers that using CASE you can make very flexible WHERE's assigning values to a variable field.
But what is you need to assign different values to different variable regarding different conditions. Then you need to use 1=1. Simple but POWERFUL.
The standard way case is used in the WHERE clause is
[code]
Select *
from table1
where 1=1
and variable1=
case
when condition1 true then value1
when condition2 true then value2
else value3
end
[/code]
I use 1=1 so that it would be easy to comment all ANDs. But if 1=1 is true ann 1=(all numbers except 1) is false then ...
[code]
Select *
from table1
where 1=
case
when variable1 = condition1 true
and variable2 = (select * froom table2
where etc etc
and et etc)
then 1
case
when(variable3 = condition2 true
and variable4 = (select * froom table2
where etc etc
and et etc))
or variable5 <> condition7
then 1
else value0
end
[/code]
WOW
when 1=1 or 1<>1 builds so powerful dynamic and flexible WHERE clause!!!
