CODEWITHSUNDEEP
sqloracle-databasecommon-table-expressionrecursive-query
Jaydip Mehta
Jaydip Mehta

Reputation: 41

WITH Clause with union all - Oracle 12c

Can anyone explain the following query?

With clause query alias recursiveBOM is being used inside query? Is there anything with UNION ALL?

 WITH recursiveBOM
    (assembly_id, assembly_name, parent_assembly) AS
 (SELECT parent.assembly_id,
         parent.assembly_name,
         parent.parent_assembly
 FROM bill_of_materials parent
 WHERE parent.assembly_id=100
 UNION ALL
 SELECT child.assembly_id,
        child.assembly_name,
        child.parent_assembly
 FROM recursiveBOM parent, bill_of_materials child
 WHERE child.parent_assembly = parent.assembly_id)
 SELECT assembly_id, parent_assembly, assembly_name
 FROM recursiveBOM;

Upvotes: 0

Views: 8668

Answers (1)

Heiner Guido
Heiner Guido

Reputation: 11

WITH recursiveBOM
 AS
 (
 SELECT parent.assembly_id,
         parent.assembly_name,
         parent.parent_assembly
 FROM bill_of_materials parent
 WHERE parent.assembly_id=100
 )
 recursiveBOM2 as
 (
 SELECT child.assembly_id,
        child.assembly_name,
        child.parent_assembly
 FROM   bill_of_materials child
  )
SELECT recursiveBOm.assembly_id, recursiveBOm.parent_assembly, recursiveBOm.assembly_name
 FROM recursiveBOm
 UNION ALL
 SELECT recursiveBOM2.assembly_id, recursiveBOM2.parent_assembly, recursiveBOM2.assembly_name
 FROM recursiveBOm, recursiveBOm2
 WHERE recursiveBOm.assembly_id = recursiveBOM2.parent_assembly
;

Upvotes: 1

Related Questions