{"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":"<p>This demo illustrates how to rewrite Oracle proprietary joins to ANSI SQL compliant joins, it&#8217;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<p>\nOracle propriety joins\n<\/p>\n<pre>\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<p>\nConvert to ANSI SQL compliant joins\n<\/p>\n<pre>\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>\n<p>Download <a href=\"http:\/\/www.sqlparser.com\/dlaction.php?fid=oraclejoinconvert_csharp&#038;ftitle= Rewrite Oracle Proprietary Joins to SQL99 C sharp version\">demo with C# source code<\/a>, <a href=\"http:\/\/www.sqlparser.com\/dlaction.php?fid=oraclejoinconvert_java&#038;ftitle= Rewrite Oracle Proprietary Joins to SQL99 java version\">Java demo<\/a>, <a href=\"http:\/\/www.sqlparser.com\/dlaction.php?fid=gspdemojoinconvert&#038;ftitle=%20Rewrite%20Oracle%20Propriety%20Joins%20to%20SQL99%20Compliant%20Joins%20executable\">demo with delphi source code<\/a>. <a href=\"http:\/\/www.dpriver.com\/gsp\/demos\/c\/oracleJoinConverter\/oracleJoinConverter.zip\">ANSI C demo<\/a><\/p>\n<p>Here are some common mistakes people made when convert Oracle propriety joins to ANSI SQL syntax:<\/p>\n<p>1. condition in where clause was broken after moving some conditions to join clause.<\/p>\n<pre>\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>\n<p>becomes this:<\/p>\n<pre>\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>\n<p>2. additional join condition was missing<\/p>\n<pre>\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>\n<p>After convert to ANSI SQL syntax:<\/p>\n<pre>\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>\n<p>3. join table is referenced before it has been joined<\/p>\n<pre>\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>\n<p>After convert to ANSI SQL syntax:<\/p>\n<pre>\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>\n<p>More information about SQL join:<\/p>\n<p>1. <a href=\"http:\/\/www.gplivna.eu\/papers\/sql_join_types.htm\" title=\"sql join types\" target=\"_blank\">SQL join types<\/a><\/p>\n<p>2. <a href=\"http:\/\/www.orafaq.com\/node\/855\" title=\"common errors seen when using OUTER-JOIN\" target=\"_blank\">Common errors seen when using OUTER-JOIN<\/a><\/p>\n<p>3. <a href=\"http:\/\/www.oracle-developer.net\/display.php?id=213\" title=\"ansi joins in oracle 9i\" target=\"_blank\">ansi joins in oracle 9i<\/a><\/p>\n<p>4. <a href=\"http:\/\/stackoverflow.com\/questions\/7584355\/sql-ansi-joins-and-the-order-of-tables-in-it\" title=\"SQL ANSI joins and the order of tables in it\" target=\"_blank\">SQL ANSI joins and the order of tables in it<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>This demo illustrates how to rewrite Oracle proprietary joins to ANSI SQL compliant joins, it&#8217;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":"<!-- This site is optimized with the Yoast SEO plugin v19.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Help you to make better use of General SQL Parser<\/title>\n<meta name=\"description\" content=\"Rewrite Oracle proprietary joins to ANSI SQL compliant joins\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/rewrite-oracle-propriety-joins-to-ansi-sql-compliant-joins\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Help you to make better use of General SQL Parser\" \/>\n<meta property=\"og:description\" content=\"Rewrite Oracle proprietary joins to ANSI SQL compliant joins\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/rewrite-oracle-propriety-joins-to-ansi-sql-compliant-joins\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and Data Blog\" \/>\n<meta property=\"article:modified_time\" content=\"2013-07-13T12:27:03+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\",\"name\":\"SQL and Data Blog\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"sameAs\":[],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"contentUrl\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"width\":251,\"height\":72,\"caption\":\"SQL and Data Blog\"},\"image\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"name\":\"SQL and Data Blog\",\"description\":\"SQL related blog for database professional\",\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/rewrite-oracle-propriety-joins-to-ansi-sql-compliant-joins\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/rewrite-oracle-propriety-joins-to-ansi-sql-compliant-joins\/\",\"name\":\"Help you to make better use of General SQL Parser\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\"},\"datePublished\":\"2010-07-22T03:09:12+00:00\",\"dateModified\":\"2013-07-13T12:27:03+00:00\",\"description\":\"Rewrite Oracle proprietary joins to ANSI SQL compliant joins\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/rewrite-oracle-propriety-joins-to-ansi-sql-compliant-joins\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/rewrite-oracle-propriety-joins-to-ansi-sql-compliant-joins\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/rewrite-oracle-propriety-joins-to-ansi-sql-compliant-joins\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dpriver.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"List of demos illustrate how to use general sql parser\",\"item\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Rewrite Oracle proprietary joins to ANSI SQL compliant joins\"}]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Help you to make better use of General SQL Parser","description":"Rewrite Oracle proprietary joins to ANSI SQL compliant joins","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/rewrite-oracle-propriety-joins-to-ansi-sql-compliant-joins\/","og_locale":"en_US","og_type":"article","og_title":"Help you to make better use of General SQL Parser","og_description":"Rewrite Oracle proprietary joins to ANSI SQL compliant joins","og_url":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/rewrite-oracle-propriety-joins-to-ansi-sql-compliant-joins\/","og_site_name":"SQL and Data Blog","article_modified_time":"2013-07-13T12:27:03+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Organization","@id":"https:\/\/www.dpriver.com\/blog\/#organization","name":"SQL and Data Blog","url":"https:\/\/www.dpriver.com\/blog\/","sameAs":[],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","contentUrl":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","width":251,"height":72,"caption":"SQL and Data Blog"},"image":{"@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"WebSite","@id":"https:\/\/www.dpriver.com\/blog\/#website","url":"https:\/\/www.dpriver.com\/blog\/","name":"SQL and Data Blog","description":"SQL related blog for database professional","publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/rewrite-oracle-propriety-joins-to-ansi-sql-compliant-joins\/","url":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/rewrite-oracle-propriety-joins-to-ansi-sql-compliant-joins\/","name":"Help you to make better use of General SQL Parser","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/#website"},"datePublished":"2010-07-22T03:09:12+00:00","dateModified":"2013-07-13T12:27:03+00:00","description":"Rewrite Oracle proprietary joins to ANSI SQL compliant joins","breadcrumb":{"@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/rewrite-oracle-propriety-joins-to-ansi-sql-compliant-joins\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/rewrite-oracle-propriety-joins-to-ansi-sql-compliant-joins\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/rewrite-oracle-propriety-joins-to-ansi-sql-compliant-joins\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dpriver.com\/blog\/"},{"@type":"ListItem","position":2,"name":"List of demos illustrate how to use general sql parser","item":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/"},{"@type":"ListItem","position":3,"name":"Rewrite Oracle proprietary joins to ANSI SQL compliant joins"}]}]}},"_links":{"self":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/357"}],"collection":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/comments?post=357"}],"version-history":[{"count":25,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/357\/revisions"}],"predecessor-version":[{"id":841,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/357\/revisions\/841"}],"up":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/235"}],"wp:attachment":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/media?parent=357"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}