{"id":1490,"date":"2012-07-09T22:00:50","date_gmt":"2012-07-10T03:00:50","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=1490"},"modified":"2012-07-10T02:22:29","modified_gmt":"2012-07-10T07:22:29","slug":"oracle-sql-query-rewrite-co-related-sub-query-to-inline-view","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-co-related-sub-query-to-inline-view\/","title":{"rendered":"Oracle SQL Query rewrite: Co-related sub-query to inline View"},"content":{"rendered":"

Co-related sub-query to inline View<\/p>\n

Co-related sub-query has join conditions to outer table. So, we can remove join conditions from co-related sub-query and make it to inline view or make it NOT EXISTS sub-query.<\/p>\n

This is an article of Oracle SQL Query rewrite serials<\/a>.<\/p>\n

Take this sql for example:<\/p>\n

    \r\nSELECT sum(salary)\r\nFROM   emp, salary, dept\r\nWHERE  emp.emp_id  = salary.emp_id \r\nAND    emp.dept_no = dept.dept_no\r\nAND    dept.location = 'SEOUL' \r\nAND    salary.yyyymmdd = (SELECT MAX(yyyymmdd)\r\n                          FROM   salary s2\r\n                          WHERE  s2.emp_id = salary.emp_id );\r\n<\/pre>\nSQL will be translated into:<\/p>\n\r\nSELECT SUM(salary)\r\nFROM   emp,\r\n       salary,\r\n       dept\r\nWHERE  emp.emp_id = salary.emp_id\r\n       AND emp.dept_no = dept.dept_no\r\n       AND dept.location = 'SEOUL'\r\n       AND NOT EXISTS (SELECT 1\r\n                       FROM   salary s2\r\n                       WHERE  s2.emp_id = salary.emp_id\r\n                              AND salary.yyyymmdd < s2.yyyymmdd) \r\n<\/pre>\nor<\/p>\n\r\nSELECT SUM(salary)\r\nFROM   emp,\r\n       salary,\r\n       dept,\r\n       (SELECT s2.emp_id,\r\n               Max(yyyymmdd) MAX_yyyymmdd\r\n        FROM   salary s2\r\n        GROUP  BY s2.emp_id) epi001\r\nWHERE  emp.emp_id = salary.emp_id\r\n       AND emp.dept_no = dept.dept_no\r\n       AND dept.location = 'SEOUL'\r\n       AND salary.yyyymmdd = epi001.MAX_yyyymmdd\r\n       AND epi001.emp_id = salary.emp_id \r\n<\/pre>\nDownload this demo: C# version<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"Co-related sub-query to inline View Co-related sub-query has join conditions to outer table. So, we can remove join conditions from co-related sub-query and make it to inline view or make it NOT EXISTS sub-query. This is an article of Oracle SQL Query rewrite serials. Take this sql for example: SELECT sum(salary) FROM emp, salary, dept WHERE emp.emp_id = salary.emp_id AND emp.dept_no = dept.dept_no AND dept.location = ‘SEOUL’ AND salary.yyyymmdd = (SELECT MAX(yyyymmdd) FROM salary s2 WHERE s2.emp_id = salary.emp_id ); SQL will be translated into: SELECT SUM(salary) FROM emp, salary, dept WHERE emp.emp_id = salary.emp_id AND emp.dept_no = dept.dept_no AND\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1445,"menu_order":0,"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