2 Replies Latest reply: Nov 23, 2012 3:26 AM by 958953 RSS

    Information Needed For Generated SQL

      I have done some odi projects.In most of the RT operation when I check the code in operator I saw that:
      insert into ...when 1=1;
      select ..from.. where 1=1;
      What does it mean by 1=1??
      there is no variable in when or where condition.

      Please Ans
        • 1. Re: Information Needed For Generated SQL
          Hi Papai,

          1=1 is a condition that is always true and that sometimes used in queries.

          ODI uses it in generated code to have valid queries with or without filters. Adding a filter will add a string : "AND filter_condition".

          Without filter:
          select * from table
          where 1 = 1
          which is the same as select * from table;

          Add a filter :
          select * from table
          where 1 = 1
          and col1 = 42
          Always-false conditions are also sometimes used by SQL developers. If you want to duplicate a table structure without duplicating data :
          create table2 as
          select * from table1
          where 1 = 2;
          No rows returned, so no rows in the new table but the structure is the same :).

          Hope it helps.

          • 2. Re: Information Needed For Generated SQL
            This helps is creating the WHERE clause.
            For Example you provide a Filter on a table i.e "A.Col is NULL"

            It will be added to the WHERE clause as

            AND A.Col is NULL.

            So now condition will become Select * from xyz WHERE *1 = 1 AND A.Col is NULL*

            Now think if 1=1 in not provided .. how will it append the

            Select * from xyz WHERE AND A.Col is NULL which is wrong

            Thus "where 1=1" makes it so they can just add "AND <condition>" to the end of the query

            When you won't provide any further filter or condition it will just show Where 1= 1