HIerarchical Query with Cumulative Quantities 2

[JKrumins]

I am familiar with the CONNECT BY ... PRIOR function and have used it to get a multi-level Product Structure (i.e Bill-of-Material) with code such as:

SELECT level, a.part_no, a.component_part, a.qty_per_assembly
FROM manuf_structure a
CONNECT BY PRIOR a.component_part = a.part_no
START WITH a.part_no = '&part_no'

That is ok, and shows me the Qty_per_Assembly of each component used within its immediate parent. But what I really want is to see the cumulative qty_per_assembly, for example:

P is parent of Q (Qty_per_assembly of Q in P is 2)
Q is parent of R (Qty_per_assembly of R in Q is 5)
R is parent of S (Qty_per_assembly of S in R is 3.5)

then I want to see:

[i]level  Part   Qty_per  Cumulative_Qty_Per[/i]
1      P      1        1
2      Q      2        2         
3      R      5        10       (2*5)
4      S      3.5      35       (2*5*3.5)

How do you do this WITHOUT using PL/SQL or creating a new function?

 

[gunjan14]

Hi,

I have not tested this but you can use this query. But you need to create a function to work.

create or replace function eval_val (expr in varchar2)
return number
as
    new_expr varchar2(200);
    expr_val varchar2(30);
begin
   new_expr := trim(expr);
   if instr(new_expr, '*', 1) = 1 then
       new_expr := substr(new_expr, 2);
   end if;
   execute immediate 'select '||new_expr||' from dual' into expr_val;
   return to_number(expr_val);
end;
/

Now you can fire a SQL statement to calculate the cumulative

select level, 
       part_no, 
       component_part, 
       [bold]eval_val(sys_connect_by_path(qty_per_assembly, '*'))[/bold]
from   manuf_structure 
CONNECT BY PRIOR a.component_part = a.part_no 
START WITH a.part_no = '&part_no'

Try and let me know if this works.

Reagrds,
Gunjan

 

[gunjan14]

oops .. sorry the formatting mistake. Can lead to confusion. The code should be.

select level, 
       part_no, 
       component_part, 
       [b]eval_val(sys_connect_by_path(qty_per_assembly, '*'))[/b]
from   manuf_structure 
CONNECT BY PRIOR a.component_part = a.part_no 
START WITH a.part_no = '&part_no'

Reagrds,
Gunjan

 

[taupirho]

Don't know if this helps but if you have a table as follows:

Table tom
---------


LEVEL_NO PART QTY
---------- ---- ----------
1 P 1
2 Q 2
3 R 5
4 S 3.5

The following query:-


select
level_no,
part,
qty,
exp(sum(ln(qty)) over(order by level_no) )prod
from tom

will return this result set:-

LEVEL_NO P QTY PROD
---------- - ---------- ----------
1 P 1 1
2 Q 2 2
3 R 5 10
4 S 3.5 35

 

[gunjan14]

Taupirho,
This does not work in every case. Let us see an example.

[b]testhier.sql[/b]
create table manuf_structure
(
    component_part   varchar2(20) not null,
    part_no          varchar2(20) null,
    qty_per_assembly number       not null
)
;
insert into manuf_structure (component_part, qty_per_assembly) values ('AA', 2);
insert into manuf_structure (component_part, part_no, qty_per_assembly) values ('AB', 'AA', 5);
insert into manuf_structure (component_part, part_no, qty_per_assembly) values ('AC', 'AA', 10);
insert into manuf_structure (component_part, part_no, qty_per_assembly) values ('AD', 'AB', 8);
insert into manuf_structure (component_part, part_no, qty_per_assembly) values ('AE', 'AC', 3);
insert into manuf_structure (component_part, part_no, qty_per_assembly) values ('AF', 'AE', 7);

commit;

Now run this to create the table and insert related records

SQL> start testhier.sql

Table created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.

SQL> select level,
  2         component_part,
  3         lpad(part_no, 2*level)           part,
  4         lpad(qty_per_assembly, 2*level)  qty
  5  from   manuf_structure
  6  connect by prior component_part = part_no
  7  start with component_part = 'AA';

     LEVEL COMPONENT_PART       PART                 QTY
---------- -------------------- -------------------- --------------------
         1 AA                                         2
         2 AB                     AA                    5
         3 AD                       AB                    8
         2 AC                     AA                   10
         3 AE                       AC                    3
         4 AF                         AE                    7

6 rows selected.

Now if I fire the query it gives me

SQL> l
  1  select level,
  2         component_part,
  3         lpad(part_no, 2*level)           part,
  4         lpad(qty_per_assembly, 2*level)  qty,
  5         exp(sum(ln(qty_per_assembly)) over (order by level)) prod
  6  from   manuf_structure
  7  connect by prior component_part = part_no
  8* start with component_part = 'AA'
SQL> /

     LEVEL COMPONENT_PART       PART                 QTY              PROD [I][COLOR=red]Actual[/color][/I]
---------- -------------------- -------------------- ---------- ---------- [I][COLOR=red]------[/color][/I]
         1 AA                                         2                  2 [I][COLOR=red]     2[/color][/I]
         2 AB                     AA                    5              100 [I][COLOR=red]    10[/color][/I]
         2 AC                     AA                   10              100 [I][COLOR=red]    20[/color][/I]
         3 AD                       AB                    8           2400 [I][COLOR=red]    80[/color][/I]
         3 AE                       AC                    3           2400 [I][COLOR=red]    60[/color][/I]
         4 AF                         AE                    7        16800 [I][COLOR=red]   420[/color][/I]

6 rows selected.

whereas by using sys_connect_by_path we get the correct result

SQL> create or replace function eval_val (expr in varchar2)
  2  return number
  3  as           
  4      expr_val varchar2(30);
  5  begin
  6      execute immediate 'select '||substr(expr, 2)||' from dual' into expr_val;
  7      return to_number(expr_val);
  8  end;
  9  /

Function created.

SQL> l
  1  select level,
  2         component_part,
  3         lpad(part_no, 2*level)           part,
  4         lpad(qty_per_assembly, 2*level)  qty,
  5         eval_val(sys_connect_by_path(qty_per_assembly, '*')) prod
  6  from   manuf_structure
  7  connect by prior component_part = part_no
  8  start with component_part = 'AA'
  9*
SQL> /

     LEVEL COMPONENT_PART       PART                 QTY              PROD
---------- -------------------- -------------------- ---------- ----------
         1 AA                                         2                  2
         2 AB                     AA                    5               10
         3 AD                       AB                    8             80
         2 AC                     AA                   10               20
         3 AE                       AC                    3             60
         4 AF                         AE                    7          420

6 rows selected.

SQL>

Regards,
Gunjan

 

[JKrumins]

Thanks, both Gunjan and Taupirho

I guess Gunjan is right about Taupirho's solution. As I understand it, twhen there are multiple 'sub-branches' of the hierarchy then all nodes at a level will get accumulated. And this is indeed the case in my data - just gave a simplified version for this example.
But the exp(sum(ln())) is very elegant!

But I've made one bigger mistake... I've posted this to the Oracle 9i forum. Our database is still at 8.1, and so doesn't have the sys_connect_by_path function. red-faced blush.

Would it be too much to ask for an 8i solution?
Maybe need to create intermediate tables?

 

[taupirho]

Could you provide a more complete (but still simple) set of sample data?

 

[JKrumins]

OK, here goes:

create table manuf_structure_test
(
    part_no          varchar2(20) null,
    component_part   varchar2(20) not null,
    qty_per_assembly number       not null
)
;


insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('A1','B1',2);
insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('A1','B2',2);
insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('A1','B3',1);
/* 3 COMPONENTS OF THE TOP-LEVEL PART, A1 */

insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('B1','C1',5);
insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('B1','C2',4);
insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('B2','C3',3);
insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('B2','C4',2);
/* B1 AND B2 HAVE 2 COMPONENTS EACH (differing qtys_per_assembly). B3 HAS NO COMPONENTS */

insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('C1','D1',2.5);
insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('C1','D2',2.5);
insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('C2','D3',2.5);
insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('C2','D4',2.5);
/* C1 AND C2 HAVE 2 COMPONENTS EACH */
/* C3,C4,D1,D2,D3,D4 ARE BOTTOM-LEVEL i.e. HAVE NO COMPONENTS */
/* STRUCTURE HAS MULTIPLE BRANCHES, SOME BRANCHES ARE OF DIFFERENT LENGTH */

Gives us a structure like:
[tt]
A1
|
B1---------B2--------B3
| |
C1--------C2 C3------C4
| |
D1----D2 D3-----D4
[/tt]

So what I'm seeking - the complete multi-level cumulative quantities for A1 - will look something like:

[tt]Top_level Component Cum.Qty Level
A1 B1 2 2
A1 B2 2 2
A1 B3 1 2
A1 C1 10 3
A1 C2 8 3
A1 C3 6 3
A1 C4 4 3
A1 D1 25 4
A1 D2 25 4
A1 D3 20 4
A1 D4 20 4
[/tt]

...and we could also choose to make the multi-level display for 'intermediate' parts like B1 etc, that would look like this:

[tt]Top_level Component Cum.Qty Level
B1 C1 5 2
B1 C2 4 2
B1 D1 12.5 3
B1 D2 12.5 3
B1 D3 10 3
B1 D4 10 3
[/tt]


Ok, that is a complete set of sample data that is - I hope - not too complicated.
============================================================

This next bit is probably not necessary, but I've added in case anyone wants a second, more complicated instance:

Now, when I use this in my real application I will be faced with many structures, some of which have repeated sub-branches. Here is an example of another structure, A2, that has a sub-branch repeated within different branches of itself and which also occur in our original structure A1.

/*A2 IS AN ADDITIONAL, MORE COMPLEX STRUCTURE, WHICH HAS REPETITION OF ONE SUB-BRANCH  */
/* AND ALSO A SUB-BRANCH IN COMMON WITH FIRST STRUCTURE A1 */  
insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('A2','B1',2);
insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('A2','B4',2);

/* insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('B1','C1',5); */
/* insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('B1','C2',4); */
/* B1 shown here for sake of completeness, but in reality it's already been created in A1 */
insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('B4','C2',4);
insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('B4','C5',10);

/* insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('C1','D1',2.5); */
/* insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('C1','D2',2.5); */
/* insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('C2','D3',2.5); */
/* insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('C2','D4',2.5); */
/* C1,C2 shown here for sake of completeness, but in reality  already been created in A1 */
insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('C5','D5',12);
insert into manuf_structure_test (part_no, component_part, qty_per_assembly) values ('C5','D6',16);

Regards,
Jan

 

[gunjan14]

Hi,

I do not have Oracle 8i version with me so cannot test this code for oracle 8i. Hence if any error comes during implementation of custom made aggregate function then let me know.
Also I cannot tell anything about the performance of this query. Probably you are better off with PL/SQL

But here we go.

1. Create the table and insert required records
File Name : newhier.sql

[I]
SQL> host cat newhier.sql
create table manuf_structure
(
    component_part   varchar2(20) not null,
    part_no          varchar2(20) null,
    qty_per_assembly number       not null
)
;
insert into manuf_structure (component_part, qty_per_assembly) values ('AA', 2);
insert into manuf_structure (component_part, part_no, qty_per_assembly) values ('AB', 'AA', 5);
insert into manuf_structure (component_part, part_no, qty_per_assembly) values ('AC', 'AA', 10);
insert into manuf_structure (component_part, part_no, qty_per_assembly) values ('AD', 'AB', 8);
insert into manuf_structure (component_part, part_no, qty_per_assembly) values ('AE', 'AC', 3);
insert into manuf_structure (component_part, part_no, qty_per_assembly) values ('AF', 'AE', 7);

commit;
[/I]
SQL> start newhier.sql

Table created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.

Now you need to copy and create type and function as given below. I have copied this in a file named as newfunc.sql

[I]
SQL> host cat newfunc.sql
create or replace type MultNumberImpl as object
(
    num number, 
    static function ODCIAggregateInitialize
    (
        sctx IN OUT MultNumberImpl
    ) return number,
    member function ODCIAggregateIterate
    (
        self  IN OUT MultNumberImpl, 
        value IN     number
    ) return number,
    member function ODCIAggregateTerminate
    (
        self        IN  MultNumberImpl,
        returnValue OUT number, 
        flags       IN  number
    ) return number,
    member function ODCIAggregateMerge
    (
        self IN OUT MultNumberImpl, 
        ctx2 IN     MultNumberImpl
    ) return number
);
/
create or replace type body MultNumberImpl is 
    static function ODCIAggregateInitialize 
    (
        sctx IN OUT MultNumberImpl
    ) 
    return number is 
    begin
        sctx := MultNumberImpl(null);
        return ODCIConst.Success;
    end;
    member function ODCIAggregateIterate
    (
        self  IN OUT MultNumberImpl, 
        value IN     number
    ) 
    return number is
    begin
        case when self.num is not null then 
            self.num := self.num * value;
        else  
            self.num := value;
        end case;
        return ODCIConst.Success;
    end;
    member function ODCIAggregateTerminate
    (
        self        IN  MultNumberImpl, 
        returnValue OUT number, 
        flags       IN  number) 
    return number is
    begin
        returnValue := self.num;
        return ODCIConst.Success;
    end;
    member function ODCIAggregateMerge
    (
        self IN OUT MultNumberImpl, 
        ctx2 IN     MultNumberImpl
    ) 
    return number is
    begin
        return ODCIConst.Success;
    end;
end;
/
create or replace function num_multiply (input number) 
return number
aggregate using MultNumberImpl
;
/
[/I]

SQL> start newfunc.sql

Type created.


Type body created.


Function created.

To test the result I have also create a function to evaluate sys_connect_by_path. Created in file oldfn.sql

[I]
SQL> host cat oldfn.sql
create or replace function eval_val (expr in varchar2)
return number
as           
    expr_val varchar2(30);
begin
    execute immediate 'select '||substr(expr, 2)||' from dual' into expr_val;
    return to_number(expr_val);
end;
/
[/I]
SQL> start oldfn.sql

Function created.

SQL>

Now I can fire my query to compute the cumulative quantity.

SQL> l
  1  select d.lvla                                                  lvl,
  2         d.cpa                                                   component,
  3         max(decode(d.lvla, 1, 1, d.qtya))* num_multiply(d.qtyb) prod
  4  from (
  5  select a.lvla lvla,
  6         a.cpa  cpa,
  7         a.qtya qtya,
  8         b.cpb  cpb,
  9         b.qtyb qtyb
 10  from   ( select level            lvla,
 11                  component_part   cpa,
 12                  part_no          pna,
 13                  qty_per_assembly qtya
 14           from   manuf_structure
 15           start with component_part = 'AA'
 16           connect by prior component_part = part_no
 17         ) a,
 18         ( select level            lvlb,
 19                  component_part   cpb,
 20                  part_no          pnb,
 21                  qty_per_assembly qtyb
 22           from   manuf_structure
 23           start with component_part = 'AA'
 24           connect by prior component_part = part_no
 25         ) b
 26  where  a.lvla >= b.lvlb
 27  and    (    a.pna <> b.pnb
 28           or b.pnb is null
 29         )
 30  and    ( b.cpb in ( select c.component_part
 31                      from   manuf_structure c
 32                      connect by prior c.part_no =  c.component_part
 33                      start with c.component_part = a.pna
 34                    )
 35           or (a.pna is null and b.pnb is null)
 36         )
 37  ) d
 38  group by d.lvla, d.cpa
 39*
SQL> /

       LVL COMPONENT                  PROD
---------- -------------------- ----------
         1 AA                            2
         2 AB                           10
         2 AC                           20
         3 AD                           80
         3 AE                           60
         4 AF                          420

6 rows selected.

This can be verified against what we got while using sys_connect_by_path

SQL> l
  1  select level,
  2         component_part,
  3         part_no,
  4         lpad(qty_per_assembly, 2*level)  qty,
  5         eval_val(sys_connect_by_path(qty_per_assembly, '*')) prod
  6  from   manuf_structure
  7  connect by prior component_part = part_no
  8  start with component_part = 'AA'
  9  order by level
 10*
SQL> /

     LEVEL COMPONENT_PART       PART_NO              QTY                   PROD
---------- -------------------- -------------------- --------------- ----------
         1 AA                                         2                       2
         2 AB                   AA                      5                    10
         2 AC                   AA                     10                    20
         3 AD                   AB                        8                  80
         3 AE                   AC                        3                  60
         4 AF                   AE                          7               420

6 rows selected.

Hope this helps.

Regards,
Gunjan

 

[JKrumins]

Thanks for the post Gunjan.
I will explore these options in the next day or two and let you know how I get on...

br JK