WITH Clause - Sub Query Factoring

  • Sub-query Repetitions in SQL statements can be avoided
  • Improved query performance
  • Neater code
  • Repeated references to sub-query is more efficient than access required by each references
Example

WITH item_categories AS
 (SELECT mic.inventory_item_id
 , mic.organization_id
 ,  mc.segment1
 , mc.segment2
 , mc.segment3
 , mc.segment4
 , mc.description
 FROM mtl_item_categories mic, mtl_categories mc
  WHERE mc.category_id = mic.category_id )
   SELECT si.segment1
   , si.description
   , ic.segment1
   , ic.segment2
   , ic.segment3
   , ic.segment4
   , ic.description item_category
   FROM mtl_system_items_b si, item_categories ic
   WHERE si.inventory_item_id = ic.inventory_item_id
    AND si.organization_id    = ic.organization_id
    AND si.organization_id    = 122;

Comments

Popular posts from this blog

Query to get FSG report details with row/column set

How to use lexical parameters in Data template (XML document) R12

Sub Inventory Transfer API.