2 Replies Latest reply: Nov 23, 2012 3:25 AM by JeromeFr RSS

    Information Needed For Generated SQL

    Papai
      Hi,
      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;
      or
      select ..from.. where 1=1;
      What does it mean by 1=1??
      there is no variable in when or where condition.

      Please Ans
      Thanks
        • 1. Re: Information Needed For Generated SQL
          JeromeFr
          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.


          Regards,
          JeromeFr
          • 2. Re: Information Needed For Generated SQL
            958953
            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
            easily.

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