Query cost of products - recursivo

[Tharkun]

Good afternoon my friends, my knowledge is basic and I'm having difficulty creating the following query:

I have a product registration table (about 5,000 items) called Tab_Product, and in this table I have the following columns: COD_ Product (Product code) and type_product (C: purchased, P: Produced). Query needs to basically bring this table and insert an additional column named Cost_product, with the unit cost of each product, calculated as follows:

When the product is type C (purchased), the cost of it It will be searched at a table that has the cost of all purchased items, called Tab_Cust. This part is quiet, this table has the following columns: cod_product (product code) and value_costo_compra.

When the item is type P (produced), its cost will be the sum of the cost of children, calculated from a good table (Bill of Materials) called Tab_Bom, and is in that part I screwed up. This table has the following columns: cod_produto_pai, cod_produto_filho and qtd_necessaria.

If a product of Tab_Product is a produced item and is formed, for example, by 10 different components, in Tab_Bom there will be 10 records relating to this item. The 10 records will have the same cod_product in the COD_PRODUTO_PAI column, and each will have a different cod_produto and qtd_necess.

The cost of a product produced from Tab_Product will then be the sum of the cost of all your children. When a child is a purchased item, just multiply the QTD_BOM's QTD_BOM for the value_costo_cupra from the tab_cost table. When a child is produced, I would have to multiply the amount of it by the sum of the cost of their own children, that is, grandchildren of the first product in question.

In the examples I found in the forum, I saw the staff using Connect, but the good of the examples generally had the hierarchical level of the items in question, but in my good table there is no indication of the level, I believe that by the fact that an item is used as a component of various products.


was developing something in this line of reasoning, but I believe it is wrong:

Select all

SELECT T1.COD_PRODUTO, 
            CASE WHEN T1.TIPO_ITEM = 'C' 
            THEN T2.VALOR_CUSTO_COMPRA 
            ELSE SELECT SUM(CASE WHEN(T3.COD_PRODUTO_PAI = T1.COD_PRODUTO AND T1.TIPO_ITEM = 'C')  
                    THEN T3.QTD_NECESSARIA * T4.VALOR_CUSTO_COMPRA 
                    ELSE T3.QTD_NECESSARIA * SELECT...??? (LOOPING PARA CADA VEZ QUE ELE ENCONTRA UM FILHO TIPO 'P') 
                    FROM TAB_BOM T3 
                    INNER JOIN TAB_CUSTO T4 ON (T4.COD_PRODUTO = T3.COD_PRODUTO_FILHO) 
FROM TAB_PRODUTO T1 
INNER JOIN TAB_CUSTO T2 ON (T2.COD_PRODUTO = T1.COD_PRODUTO)

I simplified the name of the tables in the example to facilitate understanding.

If someone can help, I will be very grateful.

[dr_gori]

There are several ways to do this.
Let's separate the 2 problems for us to understand better:

The select below seeks the cost of the purchased items:
(Please confirm if that's right You need)

Select all

SELECT  
  C1.COD_PRODUTO 
, C1.tipo_item 
, C2.custo_produto 
FROM  
  TAB_PRODUTO C1 
, tab_custo   C2 
where C1.cod_produto = C2.cod_produto  
  and C1.tipo='C' 

This select (in my understanding) seeks the price of the items produced.
(Please see if I'm wrong).

Select all

SELECT  
  P1.COD_PRODUTO 
, P1.tipo_item 
, sum(P2.qtd_necessaria * p3.custo_produto) 
FROM  
  TAB_PRODUTO P1 
, TAB_BOM     P2   
, tab_custo   P3 
where P1.cod_produto = P2.cod_produto  
  and p2.cod_produto_filho = p3.cod_produto 
  and P1.tipo='P' 
group by    
  P1.COD_PRODUTO 
, P1.tipo_item

If both are correct, simply join the 2 SELECT with a Union ALL.
If this is not this, please send us some examples of data from the cost and good table.

[Tharkun]

Good afternoon Thomas, grateful for the return.

As for purchased items (Type C), OK!

As for the items produced (type P), I can not calculate their cost only seeking their children in Tab_Bom and multiplying the amount of them at the cost of Tab_Cust, because among these children there may be some who are produced, and for each of these would have to open an instance of the main code to calculate its cost and add to the total cost of the parent ..

would be something like this:

0]]

In the example above, the cost of product 4876 (type P) of Tab_Product is the sum of the cost of children 1132, 2133, 4024, 7177 and 9438.
The cost of children 1132, 2133 and 7177 is on Tab_Cust.
The cost of the child 4024 is the sum of the cost of their own children, 2877 and 5900.
The cost of 5900 will be the sum of 2133, 1760 and 8450, and so on ...

[dr_gori]

In this case, you will have to use Connect by , or worse, you will have to make a program calling it itself ( recursiveness ).
based on this information you sent, we can create a table with this data to simulate.
I'll try to do this later, then I give a return.

[dr_gori]

Here is the creation of the sample tables and the inserts ...
Let's work on these data. I put the price of everything being 1.

This is the structure created in the inserts:

Select all

PRODUTO 4876 (P) 
└------------2 UN PRODUTO 1132 (C) 
└------------1 UN PRODUTO 2133 (C) 
└------------2 UN PRODUTO 4024 (P) 
- - - - - - - - - - - └------------8 UN PRODUTO 2877 (C) 
- - - - - - - - - - - └------------1 UN PRODUTO 5900 (P) 
- - - - - - - - - - - - - - - - - - - - - - └------------4 UN PRODUTO 2133 (C) 
- - - - - - - - - - - - - - - - - - - - - - └------------1 UN PRODUTO 1760 (C) 
- - - - - - - - - - - - - - - - - - - - - - └------------6 UN PRODUTO 8450 (C) 
└------------5 UN PRODUTO 7177 (C) 
└------------4 UN PRODUTO 9438 (P) 
- - - - - - - - - - - └------------3 UN PRODUTO 6502 (C) 
- - - - - - - - - - - └------------3 UN PRODUTO 2877 (C

CREATE TABLE / INSTERTS:

Select all

create table tab_produto ( 
  cod_produto number 
, tipo        varchar2(1) 
); 
 
create table tab_custo ( 
  cod_produto number 
, custo_produto number 
); 
 
create table tab_bom ( 
  cod_produto_pai number 
, cod_produto_filho number 
, qtd_necessaria number 
);  
 
begin 
  delete from tab_produto; 
  delete from tab_custo; 
  delete from tab_bom; 
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (4876, 'P'); 
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (1132, 'C'); 
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (2133, 'C'); 
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (4024, 'P'); 
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (2877, 'C'); 
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (5900, 'P'); 
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (1760, 'C'); 
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (8450, 'C'); 
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (7177, 'C'); 
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (9438, 'P'); 
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (6502, 'C'); 
 
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (4876, 1); 
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (1132, 1); 
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (2133, 1); 
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (4024, 1); 
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (2877, 1); 
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (5900, 1); 
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (1760, 1); 
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (8450, 1); 
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (7177, 1); 
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (9438, 1); 
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (6502, 1); 
 
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (4876, 1132, 2);  
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (4876, 2133, 1); 
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (4876, 4024, 2); 
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (4876, 7177, 5); 
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (4876, 9438, 4); 
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (4024, 2877, 8); 
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (4024, 5900, 1); 
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (5900, 2133, 4); 
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (5900, 1760, 1); 
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (5900, 8450, 6); 
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (9438, 6502, 3); 
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (9438, 2877, 3); 
  commit; 
end;

]

[dr_gori]

And here's the solution to your problem.
I was able to do using Connect by: ( run the CREATE TABLE up / inserts. I had to edit them because they were wrong )

Select all

select rec.*, c.custo_produto, p.tipo 
from (  
      select  
        b.cod_produto_pai 
      , b.cod_produto_filho 
      , b.qtd_necessaria 
      , level nivel 
      , rownum ordem  
      , SYS_CONNECT_BY_PATH(cod_produto_filho, '/') TREE 
      from  tab_bom     b 
      start with b.cod_produto_pai = 4876 
      CONNECT BY prior b.cod_produto_filho = b.cod_produto_pai 
     ) rec 
,    tab_custo c 
,    tab_produto p 
where rec.cod_produto_filho = c.cod_produto(+) 
  and rec.cod_produto_filho = p.cod_produto(+) 
order by rec.ordem

The exit of this query is:

Select all

SQL> / 
 
COD_PRODUTO_PAI COD_PRODUTO_FILHO QTD_NECESSARIA NIVEL ORDEM TREE            CUSTO_PRODUTO TIPO 
--------------- ----------------- -------------- ----- ----- --------------- ------------- ---- 
           4876              1132              2     1     1 /1132                       1 C 
           4876              2133              1     1     2 /2133                       1 C 
           4876              4024              2     1     3 /4024                       1 P 
           4024              2877              8     2     4 /4024/2877                  1 C 
           4024              5900              1     2     5 /4024/5900                  1 P 
           5900              1760              1     3     6 /4024/5900/1760             1 C 
           5900              2133              4     3     7 /4024/5900/2133             1 C 
           5900              8450              6     3     8 /4024/5900/8450             1 C 
           4876              7177              5     1     9 /7177                       1 C 
           4876              9438              4     1    10 /9438                       1 P 
           9438              2877              3     2    11 /9438/2877                  1 C 
           9438              6502              3     2    12 /9438/6502                  1 C 
 
12 rows selected 
 
SQL> 

here another view using the same query: (only changing the Clause SELECT)

Select all

select  rpad(' ',3*nivel,' ')||rec.cod_produto_filho||' ('||p.tipo||')' tree2 
, rec.qtd_necessaria qtd 
, c.custo_produto    preço 
, rec.qtd_necessaria *  c.custo_produto TOTAL 
from (  
      select  
        b.cod_produto_pai 
      , b.cod_produto_filho 
      , b.qtd_necessaria 
      , level nivel 
      , rownum ordem  
      , SYS_CONNECT_BY_PATH(cod_produto_filho, '/') TREE 
      from  tab_bom     b 
      start with b.cod_produto_pai = 4876 
      CONNECT BY prior b.cod_produto_filho = b.cod_produto_pai 
     ) rec 
,    tab_custo c 
,    tab_produto p 
where rec.cod_produto_filho = c.cod_produto(+) 
  and rec.cod_produto_filho = p.cod_produto(+) 
order by rec.ordem

Select all

select  rpad(' ',3*nivel,' ')||rec.cod_produto_filho||' ('||p.tipo||')' tree2 
, rec.qtd_necessaria qtd 
, c.custo_produto    preço 
, rec.qtd_necessaria *  c.custo_produto TOTAL 
from (  
      select  
        b.cod_produto_pai 
      , b.cod_produto_filho 
      , b.qtd_necessaria 
      , level nivel 
      , rownum ordem  
      , SYS_CONNECT_BY_PATH(cod_produto_filho, '/') TREE 
      from  tab_bom     b 
      start with b.cod_produto_pai = 4876 
      CONNECT BY prior b.cod_produto_filho = b.cod_produto_pai 
     ) rec 
,    tab_custo c 
,    tab_produto p 
where rec.cod_produto_filho = c.cod_produto(+) 
  and rec.cod_produto_filho = p.cod_produto(+) 
order by rec.ordem

99]]
Note that all items produced (p) have children!

Select all

TREE2                       QTD      preço      TOTAL 
-------------------- ---------- ---------- ---------- 
   1132 (C)                   2          1          2 
   2133 (C)                   1          1          1 
   4024 (P)                   2          1          2 
      2877 (C)                8          1          8 
      5900 (P)                1          1          1 
         1760 (C)             1          1          1 
         2133 (C)             4          1          4 
         8450 (C)             6          1          6 
   7177 (C)                   5          1          5 
   9438 (P)                   4          1          4 
      2877 (C)                3          1          3 
      6502 (C)                3          1          3 
 
12 rows selected 
 
SQL> 

: -O

[Tharkun]

Thanks for the return Thomas,

I performed here and set up TreeView correctly, but I do not know why all the items appeared duplicates.

To fit the cost obtained from this subquery in the original query, I believe that the code you posted will enter with some adjustments in place of "Thomas Code":

[vitellozzi]

I use ListAgg in place of Connect BY.

The Oracle / PLSQL ListAgg function concatizes measuring values_coluna for each group based on Order_By_Clause.

Syntax
The syntax for the LISTAGG function in Oracle / PLSQL is:

Select all

LISTAGG (measure_column [, 'delimiter']) WITHIN GROUP (order_by_clause) [OVER (query_partition_clause)]

Parameters or arguments
Measure_Column
The column or expression whose values ??you want to concatenate together in the result set. Null values ??in
Measure_Column are ignored.

Delimiter
Optional. It is the delimiter to use when separating the values ??from Measure_Column when generating the results.

Order_BY_CLAUSE
It determines the order in which the concatenated values ??(ie: measure_column) are returned.

Returns
The Listagg function returns a string value.

Applies the Listagg function can be used in the following oracle / PLSQL versions:
Oracle 12C, Oracle 11g Release 2

[dr_gori]

Yes, ListAgg serves to place multiple rows in the same column, for example, separated by comma.
But in this case, we have a recursiveness table. (Father and son field).
is a hand on the wheel. In the old days we had to do a PLSQL procedure calling herself, to solve this problem.

[Tharkun]

Good afternoon, my friends, I apologize for the delay in the return ... Closing and delivery of the K Block took me more than 30 uninterrupted days, thinks of a person who barely managed to have coffee: Shock:

Analyzing the solutions presented and return soon, thank you very much for the return!