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  |
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   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
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 
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 #327792 is a reply to message #327735] |
Tue, 17 June 2008 12:07   |
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
|
|
|
|
Goto Forum:
Current Time: Mon Feb 17 04:23:53 CST 2025
|