#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    1
    Rep Power
    0

    Oracle to Postgresql migration


    Hi All,

    We are migrating a complex java/j2ee application db from oracle to postgresql .Most of the things seems to be fine ,but pgsql doesnt recognize 'connect by level' query which we used in oracle .The query which we used in oracle is

    select level as table_no,branch_code,dept_code as dept_code from
    (select dept_code,branch_code
    from t_dept t
    where t.branch_code=1
    and t.dept_code= 1)
    connect by level<=(select no_tables from t_dept t where t.branch_code=1 and t.dept_code=1)


    Can you guys help me with the above query in pgsql?

    Thanks in advance

    Sam
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    The use of connect by does not make any sense here, but as far as I can tell, the following should do the same thing:
    Code:
    select rn as table_no,
           branch_code,
           dept_code as dept_code 
    from (select dept_code,
                 branch_code,
                 row_number() over () as rn,
                 no_tables
          from t_dept t
          where t.branch_code=1
            and t.dept_code= 1
    ) t 
    where rn <= no_tables;
    As that statement will also work with Oracle you can check if it really does the same thing by comparing it with the old statement.

    And the next time please format your SQL code using [code] tags.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo