Rewrite Oracle proprietary joins to ANSI SQL compliant joins
This demo illustrates how to rewrite Oracle proprietary joins to ANSI SQL compliant joins, it’s a very useful tool if you have lots of old Oracle propriety joins that need to be converted to ANSI SQL compliant joins. You can also learn how to rewrite SQL to meet your own requirement from this demo.
Oracle propriety joins
SELECT e.employee_id,
e.last_name,
e.department_id
FROM employees e,
departments d
WHERE e.department_id = d.department_id
Convert to ANSI SQL compliant joins
SELECT e.employee_id,
e.last_name,
e.department_id
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
Download demo with C# source code, Java demo, demo with delphi source code. ANSI C demo
Here are some common mistakes people made when convert Oracle propriety joins to ANSI SQL syntax:
1. condition in where clause was broken after moving some conditions to join clause.
WHERE (pal.application_location_id = pualr.application_location_id
AND pu.jbp_uid = pualr.jbp_uid
AND pu.username = 'USERID')
becomes this:
WHERE (
AND <=== additional keyword not removed
pu.jbp_uid = pualr.jbp_uid
AND pu.username = 'USERID')
2. additional join condition was missing
SELECT m.*,
altname.last_name last_name_student,
altname.first_name first_name_student,
ccu.date_joined,
ccu.last_login,
ccu.photo_id,
ccu.last_updated
FROM summit.mstr m,
summit.alt_name altname,
smmtccon.ccn_user ccu
WHERE m.id =?
AND m.id = altname.id(+)
AND m.id = ccu.id(+)
AND altname.grad_name_ind(+) = '*'
After convert to ANSI SQL syntax:
SELECT m.*,
altname.last_name last_name_student,
altname.first_name first_name_student,
ccu.date_joined,
ccu.last_login,
ccu.photo_id,
ccu.last_updated
FROM summit.mstr m
left outer join summit.alt_name altname
on m.id = altname.id <=== missing condition: altname.grad_name_ind = '*'
left outer join smmtccon.ccn_user ccu on m.id = ccu.id
WHERE m.id =?
3. join table is referenced before it has been joined
select * from ods_trf_pnb_stuf_lijst_adrsrt2 lst , ods_stg_pnb_stuf_pers_adr pas , ods_stg_pnb_stuf_pers_nat nat , ods_stg_pnb_stuf_adr adr , ods_stg_pnb_stuf_np prs where pas.soort_adres = lst.soort_adres and prs.id(+) = nat.prs_id and adr.id = pas.adr_id and prs.id = pas.prs_id and lst.persoonssoort = 'PERSOON' and pas.einddatumrelatie is null
After convert to ANSI SQL syntax:
select *
from ods_trf_pnb_stuf_lijst_adrsrt2 lst
join ods_stg_pnb_stuf_pers_adr pas
on (pas.soort_adres = lst.soort_adres)
right outer join ods_stg_pnb_stuf_pers_nat nat
on (prs.id = nat.prs_id) <===prs.id invalid identifier
join ods_stg_pnb_stuf_adr adr
on (adr.id = pas.adr_id)
join ods_stg_pnb_stuf_np prs <==== prs was joined here
on (prs.id = pas.prs_id)
where lst.persoonssoort = 'PERSOON'
and pas.einddatumrelatie is null
More information about SQL join: