{"id":357,"date":"2010-07-21T22:09:12","date_gmt":"2010-07-22T03:09:12","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=357"},"modified":"2013-07-13T07:27:03","modified_gmt":"2013-07-13T12:27:03","slug":"rewrite-oracle-propriety-joins-to-ansi-sql-compliant-joins","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/rewrite-oracle-propriety-joins-to-ansi-sql-compliant-joins\/","title":{"rendered":"Rewrite Oracle proprietary joins to ANSI SQL compliant joins"},"content":{"rendered":"

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.<\/p>\n

\nOracle propriety joins\n<\/p>\n

    \r\nSELECT e.employee_id,\r\n       e.last_name,\r\n       e.department_id\r\nFROM   employees e,\r\n       departments d\r\nWHERE  e.department_id = d.department_id \r\n<\/pre>\n\nConvert to ANSI SQL compliant joins\n<\/p>\n\r\nSELECT e.employee_id,\r\n       e.last_name,\r\n       e.department_id\r\nFROM   employees e\r\n       JOIN departments d\r\n         ON e.department_id = d.department_id \r\n<\/pre>\nDownload demo with C# source code<\/a>, Java demo<\/a>, demo with delphi source code<\/a>. ANSI C demo<\/a><\/p>\nHere are some common mistakes people made when convert Oracle propriety joins to ANSI SQL syntax:<\/p>\n1. condition in where clause was broken after moving some conditions to join clause.<\/p>\n\r\n\r\nWHERE  (pal.application_location_id = pualr.application_location_id \r\n         AND pu.jbp_uid = pualr.jbp_uid \r\n         AND pu.username = 'USERID')\r\n<\/pre>\nbecomes this:<\/p>\n\r\nWHERE  (\r\n         AND                             <=== additional keyword not removed \r\n             pu.jbp_uid = pualr.jbp_uid \r\n         AND pu.username = 'USERID') \r\n<\/pre>\n2. additional join condition was missing<\/p>\n\r\nSELECT m.*, \r\n       altname.last_name  last_name_student, \r\n       altname.first_name first_name_student, \r\n       ccu.date_joined, \r\n       ccu.last_login, \r\n       ccu.photo_id, \r\n       ccu.last_updated \r\nFROM   summit.mstr m, \r\n       summit.alt_name altname, \r\n       smmtccon.ccn_user ccu \r\nWHERE  m.id =?\r\n       AND m.id = altname.id(+) \r\n       AND m.id = ccu.id(+) \r\n       AND altname.grad_name_ind(+) = '*'\r\n<\/pre>\nAfter convert to ANSI SQL syntax:<\/p>\n\r\nSELECT m.*,\r\n       altname.last_name  last_name_student,\r\n       altname.first_name first_name_student,\r\n       ccu.date_joined,\r\n       ccu.last_login,\r\n       ccu.photo_id,\r\n       ccu.last_updated\r\nFROM   summit.mstr m\r\nleft outer join summit.alt_name altname \r\non m.id = altname.id                                  <=== missing condition: altname.grad_name_ind  = '*'\r\nleft outer join smmtccon.ccn_user ccu on m.id = ccu.id \r\nWHERE  m.id =?\r\n<\/pre>\n3. join table is referenced before it has been joined<\/p>\n\r\nselect *\r\nfrom  ods_trf_pnb_stuf_lijst_adrsrt2 lst\r\n\t\t, ods_stg_pnb_stuf_pers_adr pas\r\n\t\t, ods_stg_pnb_stuf_pers_nat nat\r\n\t\t, ods_stg_pnb_stuf_adr adr\r\n\t\t, ods_stg_pnb_stuf_np prs\r\nwhere \r\n\tpas.soort_adres = lst.soort_adres\r\n\tand prs.id(+) = nat.prs_id\r\n\tand adr.id = pas.adr_id\r\n\tand prs.id = pas.prs_id\r\n  and lst.persoonssoort = 'PERSOON'\r\n   and pas.einddatumrelatie is null\r\n<\/pre>\nAfter convert to ANSI SQL syntax:<\/p>\n\r\nselect *    \r\n  from ods_trf_pnb_stuf_lijst_adrsrt2 lst    \r\n  join ods_stg_pnb_stuf_pers_adr pas    \r\n    on (pas.soort_adres = lst.soort_adres)    \r\n right outer join ods_stg_pnb_stuf_pers_nat nat    \r\n    on (prs.id = nat.prs_id)                     <===prs.id invalid identifier\r\n      join ods_stg_pnb_stuf_adr adr\r\n        on (adr.id = pas.adr_id)\r\n      join ods_stg_pnb_stuf_np prs               <==== prs was joined here\r\n        on (prs.id = pas.prs_id)\r\nwhere lst.persoonssoort = 'PERSOON'\r\n   and pas.einddatumrelatie is null\r\n<\/pre>\nMore information about SQL join:<\/p>\n1. SQL join types<\/a><\/p>\n2. Common errors seen when using OUTER-JOIN<\/a><\/p>\n3. ansi joins in oracle 9i<\/a><\/p>\n4. SQL ANSI joins and the order of tables in it<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"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\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":235,"menu_order":200,"comment_status":"closed","ping_status":"open","template":"gsp_feature_page_tt.php","meta":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nHelp you to make better use of General SQL Parser<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n