Ilyes Ferchiou Ilyes Ferchiou - 5 months ago 12
SQL Question

Finding children that meet a specefic criteria in ORACLE

Suppose I have two tables :
The first one is named

sales
and has two columns named
product
and
quantity

The second one is named
nomenclature
and has two columns named
compound
and
component
.

Every
compound
can have several
components
and every
component
can be itself a
compound
with several
components
which makes the second table a hierarchy.
Let's take for example the following tables :

TABLE : SALES
PRODUCT - QUANTITY
P1 - 200
P2 - 300
F3 - 400
P5 - 500


TABLE : NOMENCLATURE
COMPOUND - COMPONENT
P1 - A1
P1 - B2
P2 - D4
A1 - F6
B2 - Q7
D4 - F8
Q7 - F9
F9 - H10
P3 - F11


I want to find for every row in
SALES.PRODUCT
every component in its hierarchy that starts with F and return them and/or return itself if the product itself starts with F or ignore the row otherwise. The final result should look like this :

PRODUCT - QUANTITY - COMPONENT
P1 - 200 - F6
P1 - 200 - F9
P2 - 300 - F8
F3 - 400 - F3
F3 - 400 - F11


(P5 is ignored since it doesn't start with F and doesn't have any children that start with F)

How to do obtain this result in PL/SQL (ORACLE) with the most efficient way (NOMENCLATURE actually has over 500k rows)

MT0 MT0
Answer

You could do something like this:

WITH combined ( product, component, quantity ) AS (
  SELECT product, product, quantity FROM sales
UNION ALL
  SELECT compound, component, NULL FROM nomenclature
)
SELECT CONNECT_BY_ROOT( product ) AS product,
       CONNECT_BY_ROOT( quantity ) AS quantity,
       component
FROM   combined
WHERE  component LIKE 'F%'
START WITH quantity IS NOT NULL
CONNECT BY NOCYCLE PRIOR component = product;
Comments