Home » SQL & PL/SQL » SQL & PL/SQL » Analytical query to get sum of parts (Oracle 10.1.0.2.0)
Analytical query to get sum of parts [message #327708] Tue, 17 June 2008 06:01 Go to next message
tejas_gandhi
Messages: 8
Registered: August 2007
Junior Member
Hi,

I have table parts_detail where part_id1,part_id2 and quantity stored.
Like in following table, for Part_id 'A' 10D required and for 1D 2E required. So for Part 'A' total of 10*2=20 part 'E' required. Assuming 'E' is leaf node in the tree for every other parts.



SQL> select * from parts_detail;

PART PART   QUANTITY
_ID1 _ID2
---- ---- ----------
A    D            10
B    E            10
C    D             5
D    E             2



create table parts_detail (part_id1 varchar2(4),part_id2 varchar2(4),quantity number);

insert into parts_detail values ('A','D',10);

insert into parts_detail values ('B','E',10);

insert into parts_detail values ('C','D',5);

insert into parts_detail values ('D','E',2);



I am able to get sum of quantity using single sql query like..
SQL> select sum(quantity) from parts_detail connect by prior part_id2=part_id1 start with part_id1='A';

SUM(QUANTITY)
-------------
           12


But the output should be 20. Is there any way to achieve it using single query or pl/sql required?

Thanks.
Re: Analytical query to get sum of parts [message #327712 is a reply to message #327708] Tue, 17 June 2008 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a difficult problem. I once posted a solution for a closer one:
DROP TABLE component;
DROP TABLE product;
CREATE TABLE product (
  pid INTEGER PRIMARY KEY,
  name VARCHAR2(20)
  )
/
CREATE TABLE component (
  pid INTEGER REFERENCES product,
  cid INTEGER REFERENCES product,
  cnt INTEGER,
  PRIMARY KEY (pid, cid)
  )
/
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');
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);
INSERT INTO component VALUES (1, 4, 4);
COMMIT;
SELECT * FROM product ORDER BY pid;
SELECT * FROM component ORDER BY pid, cid;

SQL> COLUMN product   FORMAT A20
SQL> COLUMN component FORMAT A20
SQL> BREAK ON product
SQL> WITH 
  2    step1 AS (
  3      SELECT CONNECT_BY_ROOT pid pid, cid,
  4             SYS_CONNECT_BY_PATH(cnt,'*')||'*' p
  5      FROM component
  6      WHERE CONNECT_BY_ISLEAF = 1
  7      CONNECT BY PRIOR cid = pid
  8      START WITH pid IN ( SELECT pid FROM component 
  9                          MINUS
 10                          SELECT cid FROM component )
 11    ),
 12    step2 AS (
 13      SELECT pid, cid, p,
 14             ROW_NUMBER () OVER (PARTITION BY pid, cid ORDER BY p) rn
 15      FROM step1
 16    ),
 17    lines AS (
 18      SELECT ROWNUM linenb
 19      FROM DUAL 
 20      CONNECT BY LEVEL <= 
 21                 ( SELECT MAX(LENGTH(p)-LENGTH(REPLACE(p,'*',''))-1)
 22                   FROM step1 )
 23    ),
 24    step3 AS (
 25      SELECT pid, cid, rn,
 26             TO_NUMBER(SUBSTR(p, INSTR(p, '*', 1, linenb)+1,
 27                                 INSTR(p, '*', 1, linenb+1)
 28                                 -INSTR(p, '*', 1, linenb)-1))
 29               cnt
 30      FROM lines, step2
 31      WHERE linenb < LENGTH(p)-LENGTH(REPLACE(p,'*',''))
 32    ),
 33    step4 AS (
 34      SELECT pid, cid, rn, ROUND(EXP(SUM(LN(cnt)))) cnt
 35      FROM step3
 36      GROUP BY pid, cid, rn
 37    )
 38  SELECT p.name||' (ID='||s4.pid||')' product,
 39         c.name||' (ID='||s4.cid||')' component,
 40         SUM(s4.cnt) cnt
 41  FROM step4 s4, product p, product c
 42  WHERE p.pid = s4.pid
 43    AND c.pid = s4.cid
 44  GROUP BY s4.pid, s4.cid, p.name, c.name
 45  ORDER BY s4.pid, s4.cid
 46  /

PRODUCT              COMPONENT                   CNT
-------------------- -------------------- ----------
product A (ID=1)     product B (ID=2)              1
                     product G (ID=7)             64
                     product H (ID=8)             20
product E (ID=5)     product B (ID=2)              3

Execute each step to see how it works.

Regards
Michel
Re: Analytical query to get sum of parts [message #327734 is a reply to message #327712] Tue, 17 June 2008 07:06 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Michael,

By any chance are you a mind reader ?

Quote:
Like in following table, for Part_id 'A' 10D required and for 1D 2E required. So for Part 'A' total of 10*2=20 part 'E' required. Assuming 'E' is leaf node in the tree for every other parts.

I did not understand anything from this question. Probably I need a coffee Smile

Regards

Raj

[Edit : ] Typo

[Updated on: Tue, 17 June 2008 07:06]

Report message to a moderator

Re: Analytical query to get sum of parts [message #327735 is a reply to message #327734] Tue, 17 June 2008 07:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I read it several times, adding space and punctuation here and there, and finally understood this:
- "for Part_id 'A' 10D required": to build a 'A': 'D' is required 10 times (line 1)
- "for 1D 2E required": to build a 'D', 2 'E' are required (last line)
- "So for Part 'A' total of 10*2=20 part 'E' required": and so to build a 'A', we need 10*2=20 'E' elements.

Easy for me to link it to the product/components problem I had to solve a couple of years ago. Smile

Regards
Michel
Re: Analytical query to get sum of parts [message #327792 is a reply to message #327735] Tue, 17 June 2008 12:07 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production



SQL> l
  1  create or replace function test_sum(p_val varchar2)
  2  return number
  3  is
  4  v_number number;
  5  begin
  6    execute immediate 'select ' || p_val || ' from dual ' into v_number;
  7    return v_number;
  8  end
  9
 10  ;
 11*
/

Function created.

SQL> select * from product;

       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> select * from component;

       PID        CID        CNT
---------- ---------- ----------
         5          2          3
         4          7          5
         3          8         10
         3          4          3
         3          7          7
         1          3          2
         1          2          1
         1          4          4

8 rows selected.

SQL> select parent_rec, traverse_path component , sum(test_sum(expr)) cnt
  2  from
(
  3    4  select nvl(substr(ltrim(sys_connect_by_path(p.name,','),','),1,
  5  instr(ltrim(sys_connect_by_path(p.name,','),','),',',1) - 1), p.name) parent_rec,
  6   p.name child, level l,  connect_by_isleaf,
  7   p1.name || ' (ID=' || p1.pid || ')' traverse_path ,
  8   ltrim(sys_connect_by_path(cnt,'*'),'*') expr from
  9  product p, component c, product p1
 10  where p.pid = c.pid
 11  and
 12  p1.pid = c.cid
 13  and connect_by_isleaf = 1
 14  start with p.pid in (select pid from component minus select cid from component)
 15  connect by c.pid = prior c.cid
 16  )
 17  group by parent_rec, traverse_path
 18  /

PARENT_REC                     COMPONENT                         CNT
------------------------------ ------------------------------ ------
product A                      product B (ID=2)                    1
product A                      product G (ID=7)                   64
product A                      product H (ID=8)                   20
product E                      product B (ID=2)                    3


I am not 100% sure whether this will for all the scenarios. What do you think ?

Regards

Raj
Re: Analytical query to get sum of parts [message #327793 is a reply to message #327792] Tue, 17 June 2008 12:26 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hard for me to understand a not formatted query. Wink
And I prefer to use factoring clause to clearly show the successive steps of execution, easier to understand than subqueries.
But the most difficult part of my query is to avoid PL/SQL function and use "pure" SQL.

Regards
Michel
Previous Topic: Problem accessing external tables over network
Next Topic: help with this query & unwanted records (merged)
Goto Forum:
  


Current Time: Sat Dec 10 16:39:43 CST 2016

Total time taken to generate the page: 0.08801 seconds