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

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

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

NVL's equivalent is COALESCE

Cheers
Sathyaram