I converted Oracle SQL to db2 SQL with your help. I am getting syntax errors. help please.


SELECT nl.title,
COALESCE(CHAR(ac.currentcy_date,'MM/DD/YYYY'),' '),
COALECE(CHAR(ORA8.ADD_MONTHS(ac.currentcy_date, co.current_cy_interval),'MM/DD/YYYY'),'Needs Initial Review'),
n.menu_position
FROM currentcy_review ac RIGHT OUTER JOIN ON ac.applID = 3,
cy_review_sections crs LEFT OUTER JOIN currentcy_review ac1 ON ac1.cy_section_link_id = crs.cy_section_link_id, navbar_links nl, navbars n,
ap_profile ap, company co
WHERE crs.cy_section_link_id = nl.link_id
AND nl.link_id = n.link_id
AND n.navbar_id = 9 and ap.appl_id = 3
AND co.company_id = ap.company_id
AND EXISTS (SELECT 'x'
FROM ap_profile ac2, company co2
WHERE ac2.appl_id = 3
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,
COALECE(CHAR(ac.currentcy_date,'MM/DD/YYYY'),' '),
'Interval Not Specified',
n.menu_position
FROM currentcy_review ac RIGHT OUTER JOIN ON ac.applID = 3,
currentcy_review ac1 RIGHT OUTER JOIN cy_review_sections crs ON ac.cy_section_link_id = crs.cy_section_link_id,
navbar_links nl,
navbars n
WHERE crs.cy_section_link_id = nl.link_id
AND nl.link_id = n.link_id
AND n.navbar_id = 9
AND NOT EXISTS (SELECT 'x'
FROM ap_profile ac3, company co3
WHERE ac3.appl_id = 3
AND co3.company_id = ac3.company_id
AND co3.current_cy_interval > 0 AND co3.current_cy_interval is not null)
ORDER BY 4

Did you get any error message ?

Cheers
Sathyaram

Thank you very much Sathyaram,

Following is the error message I get:

SQL0010N The string constant beginning with "x' FROM ap_profile
ac2, company co2 WHERE ac2.appl_id = 3 AND co2.comp" does not
have an ending string delimiter.

Explanation:

The statement contains a string constant, beginning with
"<string>", that is not terminated properly.

The statement cannot be processed.

User Response:

Examine the statement for missing apostrophes in the indicated
string constant.

sqlcode : -10

sqlstate : 42603

Besides this, while using MTK_AS400 toolkit to convert, it complains about the syntax on outer join

FROM cy_review_sections crs LEFT OUTER JOIN currentcy_review ac1 ON ac1.cy_section_link_id = crs.cy_section_link_id,

I am very new to db2 SQL, so please excuse me, if I ask any stupid question