SQL: the poweful 1=1



IBM's flagship relational database management system

SQL: the poweful 1=1

Postby polparioritsas » Thu Dec 06, 2018 12:03 am

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!!!
polparioritsas
 
Posts: 3
Joined: Tue Nov 28, 2017 12:28 am
Has thanked: 0 time
Been thanked: 0 time

Re: SQL: the poweful 1=1

Postby Jim Ruddy » Thu Dec 20, 2018 3:23 am

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
Jim Ruddy
 
Posts: 5
Joined: Tue Jan 16, 2018 11:45 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2