Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Unanswered: Converting Oracle SQL to DB2 SQL

    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. #2
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    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
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  3. #3
    Join Date
    Oct 2003
    Posts
    8

    Re: Converting Oracle SQL to DB2 SQL

    Originally posted by 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

  4. #4
    Join Date
    Oct 2003
    Posts
    8

    Re: Converting Oracle SQL to DB2 SQL

    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

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Converting Oracle SQL to DB2 SQL

    NVL's equivalent is COALESCE

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •