| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to code SQL for a BOM Explosion?
Glenn Heinze a écrit dans le message <7pgpmh$lgt$1_at_cougar.golden.net>...
>If I have a table of finished goods named Product like this:
>
>ProductID Name
>======== ====
>1 Product A
>2 Product B
>3 Product C
>4 Product D
>
>and a components table like this:
>
>ProductID PartID Count
>======== ===== =====
>1 2 1
>1 3 2
>3 4 3
>
>I would like to report that "Product A" is composed of 1 piece of "Product
>B" and 6 pieces of "Product D" (ie 2 pieces of "Product C" each of which is
>itself composed of 3 pieces of "Product D".
>
>There is no fixed depth to the recursion.
>
>Is there a way to write a SELECT statement for this where a can avoid
>procedures? Is this where I could use the "connect by ... prior..." clause?
>Is there a way to do it with "standard" sql? Or perhaps the two table
>structure of "Product" and "Component" is not the most elegant approach?
>
>Thanks in advance for any help
>-Glenn
>
>
It is not possible to write a simple SELECT statement because of
several limitations :
- you cannot have the root, the leaf and all the path in the same result row
with a CONNECT BY ;
- you cannot use GROUP BY or join with the CONNECT BY ;
- there is no PROD group function to calculate the product of values like the
SUM one calculate the sum and even less a SUM/PROD function you need in your
case (if, for instance, "Product A" is also directly made with 2 pieces of
"Product D", total of pieces of "Product D" = (2*3)+2 = 8).
...
I got a result, if you admit to create a simple package to simulate a prod group function and an intermediate table.
Here my script following by the execution report (i add an intermediate view to show you the result at this level and to simplify the following query but this is not mandatory):
/* Table of products */
drop table product;
create table product (pid number, name varchar2(20));
insert into product values (1, 'Product A'); insert into product values (2, 'Product B'); insert into product values (3, 'Product C'); insert into product values (4, 'Product D'); insert into product values (5, 'Product E'); insert into product values (6, 'Product F'); insert into product values (7, 'Product G'); insert into product values (8, 'Product H');commit;
create table component (pid number, cid number, cnt number); insert into component values (5, 2, 3); insert into component values (4, 7, 5); insert into component values (3, 8, 10); insert into component values (3, 4, 3); insert into component values (3, 7, 7); insert into component values (1, 3, 2); insert into component values (1, 2, 1);commit;
/* Generate a group number */
group_nb number;
function grpnb (new_group integer) return number;
pragma restrict_references(grpnb,WNDS,RNDS);
/* Simulate a grouping product */
group_prod number;
function grpprod (value number, new_group integer) return number;
pragma restrict_references(grpprod,WNDS,RNDS);
end pkg_group;
/
create or replace package body pkg_group as
/* Generate a group number */
function grpnb (new_group integer) return number
is
begin
if ( new_group <> 0 ) then
group_nb := group_nb + 1;
end if;
return group_nb;
if ( new_group <> 0 ) then
group_prod := value;
else
group_prod := group_prod * value;
end if;
return group_prod;
/ /* Create an intermediate view to simplify the */ /* text of the creation of the result table */create or replace view v as
(select pid from product group by pid)
/ /* Make the result with only the leaves */ /* Leaves are components who have no component */exec pkg_group.group_nb := 0;
pkg_group.grpnb(decode(c.nb, a.nb, 1, 0)) grp,
pkg_group.grpprod(c.cnt, decode(c.nb, a.nb, 1, 0)) cnt
from v c, v b, v a
a.lvl = 1
and /* Search all the components of the selected product */
( b.nb >= a.nb
and b.nb < (select nvl(min(d.nb),999999) from v d
where d.lvl = 1 and d.nb > a.nb ) )
and /* Search the intermediate components */
( c.nb between a.nb and b.nb
and ( c.nb = b.nb
or ( c.lvl < b.lvl
and not exists ( select 1 from v d
where d.nb > c.nb and d.nb < b.nb
and d.lvl = c.lvl ) ) )
)
and /* Keep only the leaves */
not exists ( select 1 from v d
where d.nb > b.nb and d.lvl > b.lvl
and d.nb < ( select nvl(min(e.nb),999999)
from v e
where e.lvl <= b.lvl
and e.nb > b.nb )
)
where b.grp = a.grp )
and b.pid = a.pid
and c.pid = a.cid
group by b.name, c.name
/
And here's the report:
SQL>select * from product order by pid
2 /
PID NAME
---------- --------------------
1 Product A
2 Product B
3 Product C
4 Product D
5 Product E
6 Product F
7 Product G
8 Product H
8 rows selected.
SQL>/* Content of table component */
SQL>select * from component order by pid, cid
2 /
PID CID CNT
---------- ---------- ----------
1 2 1
1 3 2
3 4 3
3 7 7
3 8 10
4 7 5
5 2 3
7 rows selected.
SQL>/* Display intermediate result */
SQL>select * from v
2 /
NB LVL PID CID CNT ---------- ---------- ---------- ---------- ----------
1 1 1 3 2
2 2 3 8 10
3 2 3 4 3
4 3 4 7 5
5 2 3 7 7
6 1 1 2 1
7 1 3 8 10
8 1 3 4 3
9 2 4 7 5
10 1 3 7 7
11 1 4 7 5
12 1 5 2 3
12 rows selected.
SQL>/* Display the result */ SQL>break on Product SQL>select b.name "Product", c.name "Component", sum(cnt) "Count"2 from product c, product b, t a
4 where b.grp = a.grp )5 and b.pid = a.pid
Product Component Count
-------------------- -------------------- ----------
Product A Product B 1
Product G 44
Product H 20
Product C Product G 22
Product H 10
Product D Product G 5
Product E Product B 3
7 rows selected.
This is not very beautiful. A PL/SQL procedure will make it easier! The only advantage is you can see that you can related root and its leaves of a hierarchical query. Received on Mon Aug 23 1999 - 04:29:11 CDT
 |
 |