Tags: advance, converting, convet, database, db2, follwoing, mysql, oracle, select, sql

Converting Oracle SQL to DB2 SQL

On Database » DB2

4,666 words with 4 Comments; publish: Tue, 04 Dec 2007 19:08:00 GMT; (25062.50, « »)

Help Please,

I have follwoing Oracle SQL, I want to convet it to DB2 SQL. Thanks in advance.

SELECT nl.title, NVL(TO_CHAR(ac.currentcy_date,'MM/DD/YYYY'),' '),

NVL(TO_CHAR(ADD_MONTHS(ac.currentcy_date, co.current_cy_interval),'MM/DD/YYYY'),'Needs Initial Review'), n.menu_position

FROM appl_currentcy_review ac, currentcy_review_sections crs, navbar_links nl, navbars n,

appl_profile ap, company co

WHERE ac.appl_id(+) = '+ applID +' --java var

AND ac.currentcy_section_link_id(+) = crs.currentcy_section_link_id

AND crs.currentcy_section_link_id = nl.link_id

AND nl.link_id = n.link_id

AND n.navbar_id = 9 and ap.appl_id = '+ applID +' --java var

AND co.company_id = ap.company_id

AND EXISTS (SELECT 'x' FROM appl_profile ac2, company co2

WHERE ac2.appl_id = '+ applID +'

AND co2.company_id = ac2.company_id

AND co2.current_cy_interval > 0 AND co2.current_cy_interval is not null)

UNION

SELECT nl.title, NVL(TO_CHAR(ac.currentcy_date,'MM/DD/YYYY'),' '), 'Interval Not Specified', n.menu_position

FROM appl_currentcy_review ac, currentcy_review_sections crs, navbar_links nl, navbars n

WHERE ac.appl_id(+) = '+ applID +'

AND ac.currentcy_section_link_id(+) = crs.currentcy_section_link_id

AND crs.currentcy_section_link_id = nl.link_id

AND nl.link_id = n.link_id

AND n.navbar_id = 9

AND NOT EXISTS (SELECT 'x' FROM appl_profile ac3, company co3

WHERE ac3.appl_id = '+ applID +'

AND co3.company_id = ac3.company_id

AND co3.current_cy_interval > 0 AND co3.current_cy_interval is not null)

ORDER BY 4

All Comments

Leave a comment...

  • 4 Comments
    • sunitasorathia,

      Get a download of the SQL cookbooks (by G. Birchall) and you will everything you need to convert the ORACLE SQL to the DB2 syntax,like how to use general outer join syntax instead of ORACLE's (+) , the substitute for NVL etc. You will have to be creative with the TO_CHAR conversion I guess.

      Look for link to SQL cookbooks in the list of DB2 niceties Sathyarams has rounded up for us

      #1; Tue, 11 Dec 2007 17:49:00 GMT
    • === Original Words ===

      sunitasorathia

      Help Please,

      I have follwoing Oracle SQL, I want to convet it to DB2 SQL. Thanks in advance.

      SELECT nl.title, NVL(TO_CHAR(ac.currentcy_date,'MM/DD/YYYY'),' '),

      NVL(TO_CHAR(ADD_MONTHS(ac.currentcy_date, co.current_cy_interval),'MM/DD/YYYY'),'Needs Initial Review'), n.menu_position

      FROM appl_currentcy_review ac, currentcy_review_sections crs, navbar_links nl, navbars n,

      appl_profile ap, company co

      WHERE ac.appl_id(+) = '+ applID +' --java var

      AND ac.currentcy_section_link_id(+) = crs.currentcy_section_link_id

      AND crs.currentcy_section_link_id = nl.link_id

      AND nl.link_id = n.link_id

      AND n.navbar_id = 9 and ap.appl_id = '+ applID +' --java var

      AND co.company_id = ap.company_id

      AND EXISTS (SELECT 'x' FROM appl_profile ac2, company co2

      WHERE ac2.appl_id = '+ applID +'

      AND co2.company_id = ac2.company_id

      AND co2.current_cy_interval > 0 AND co2.current_cy_interval is not null)

      UNION

      SELECT nl.title, NVL(TO_CHAR(ac.currentcy_date,'MM/DD/YYYY'),' '), 'Interval Not Specified', n.menu_position

      FROM appl_currentcy_review ac, currentcy_review_sections crs, navbar_links nl, navbars n

      WHERE ac.appl_id(+) = '+ applID +'

      AND ac.currentcy_section_link_id(+) = crs.currentcy_section_link_id

      AND crs.currentcy_section_link_id = nl.link_id

      AND nl.link_id = n.link_id

      AND n.navbar_id = 9

      AND NOT EXISTS (SELECT 'x' FROM appl_profile ac3, company co3

      WHERE ac3.appl_id = '+ applID +'

      AND co3.company_id = ac3.company_id

      AND co3.current_cy_interval > 0 AND co3.current_cy_interval is not null)

      ORDER BY 4

      #2; Tue, 11 Dec 2007 17:50:00 GMT
    • hi,

      insted of nvl in Oracle in db2 value function

      select nvl(comm,0) from emp;

      in DB2

      select value(comm,0) from emp;

      to change date format , let me know your DB2 UDB or OS/390 version

      if it is DB2 UDB V8.x

      timestamp_format

      based on your DB2 version , I can tell you , what flexibility in DB2

      --Raju

      #3; Tue, 11 Dec 2007 17:51:00 GMT
    • NVL's equivalent is COALESCE

      Cheers

      Sathyaram

      #4; Tue, 11 Dec 2007 17:52:00 GMT