This discussion is archived
2 Replies Latest reply: Nov 23, 2012 1:25 AM by JeromeFr RSS

Information Needed For Generated SQL

Papai Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points