Skip to content
No results
We use cookies to ensure that we give you the best experience on our website.

# 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
    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
    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)
    More information about SQL join:
    1. SQL join types
    2. Common errors seen when using OUTER-JOIN
    3. ansi joins in oracle 9i
    4. SQL ANSI joins and the order of tables in it