{"id":1464,"date":"2012-07-09T21:45:39","date_gmt":"2012-07-10T02:45:39","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=1464"},"modified":"2012-07-10T01:42:32","modified_gmt":"2012-07-10T06:42:32","slug":"oracle-sql-query-rewrite-not-exists-sub-query-to-not-in-sub-query","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-not-exists-sub-query-to-not-in-sub-query\/","title":{"rendered":"Oracle SQL Query rewrite: (NOT) EXISTS sub-query to (NOT) IN sub-query"},"content":{"rendered":"

(NOT) EXISTS sub-query to (NOT) IN 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 tax_line_id\r\n  FROM zx_lines zl\r\n WHERE application_id = :B3\r\n   AND entity_code = :B2\r\n   AND event_class_code = :B1\r\n   AND (process_for_recovery_flag = 'Y'\r\n         OR item_dist_changed_flag = 'Y')\r\n   AND EXISTS (SELECT 1\r\n                 FROM zx_lines_det_factors\r\n                WHERE application_id = zl.application_id\r\n                  AND entity_code = zl.entity_code\r\n                  AND event_class_code = zl.event_class_code\r\n                  AND trx_id = zl.trx_id\r\n                  AND trx_line_id = zl.trx_line_id\r\n                  AND trx_level_type = zl.trx_level_type\r\n                  AND event_id = :B4);\r\n<\/pre>\nSQL will be translated into:<\/p>\n\r\nSELECT tax_line_id\r\nFROM   zx_lines zl\r\nWHERE  application_id = :B3\r\n       AND entity_code = :B2\r\n       AND event_class_code = :B1\r\n       AND ( process_for_recovery_flag = 'Y'\r\n              OR item_dist_changed_flag = 'Y' )\r\n       AND ( zl.application_id, zl.entity_code, zl.event_class_code, zl.trx_id,\r\n             zl.trx_line_id, zl.trx_level_type ) IN (SELECT application_id,\r\n                                                            entity_code,\r\n                                                            event_class_code,\r\n                                                            trx_id,\r\n                                                            trx_line_id,\r\n                                                            trx_level_type\r\n                                                     FROM   zx_lines_det_factors\r\n                                                     WHERE  event_id = :B4) \r\n<\/pre>\nDownload this demo: C# version<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"(NOT) EXISTS sub-query to (NOT) IN sub-query This is an article of Oracle SQL Query rewrite serials. Take this sql for example: SELECT tax_line_id FROM zx_lines zl WHERE application_id = :B3 AND entity_code = :B2 AND event_class_code = :B1 AND (process_for_recovery_flag = ‘Y’ OR item_dist_changed_flag = ‘Y’) AND EXISTS (SELECT 1 FROM zx_lines_det_factors WHERE application_id = zl.application_id AND entity_code = zl.entity_code AND event_class_code = zl.event_class_code AND trx_id = zl.trx_id AND trx_line_id = zl.trx_line_id AND trx_level_type = zl.trx_level_type AND event_id = :B4); SQL will be translated into: SELECT tax_line_id FROM zx_lines zl WHERE application_id = :B3 AND entity_code = :B2 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