Page 1 of 1

SQL: the poweful 1=1

PostPosted: Thu Dec 06, 2018 12:03 am
by polparioritsas
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

Select *
  from table1
 where 1=1
    and variable1=
          case
           when condition1 true then value1
          when condition2 true then value2
          else value3
          end
 


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 ...


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
 


WOW
when 1=1 or 1<>1 builds so powerful dynamic and flexible WHERE clause!!!

Re: SQL: the poweful 1=1

PostPosted: Thu Dec 20, 2018 3:23 am
by Jim Ruddy
Using 1=1 or 1 <> 1 in a predicate can also cause changes in access path determination so it is wise to check the EXPLAIN information before and after to verify you have a performant access path.

Jim