Home » SQL & PL/SQL » SQL & PL/SQL » How to bypass the selective rows from SQL Analytic function (Oracle 11.2.0.1.0)
How to bypass the selective rows from SQL Analytic function [message #658689] |
Fri, 23 December 2016 03:32 |
jimit_shaili
Messages: 237 Registered: June 2006 Location: India, Ahmedabad
|
Senior Member |
|
|
Dear All,
Herewith I've a as case, wherein i used the sum(sum()) analytic function. Now I would like to bypass some rows based on the certain logic.
CREATE TABLE TEMP1
(
PID NUMBER(8),
QUANTITY NUMBER(12,3)
);
Insert into TEMP1 (PID, QUANTITY) Values (1, 100);
Insert into TEMP1 (PID, QUANTITY) Values (2, 200);
Insert into TEMP1 (PID, QUANTITY) Values (3, 300);
Insert into TEMP1 (PID, QUANTITY) Values (4, 400);
Insert into TEMP1 (PID, QUANTITY) Values (5, 500);
CREATE TABLE TEMP2
(
PID NUMBER(8),
PDESC VARCHAR2(10 BYTE),
STATUS NUMBER(1)
);
Insert into TEMP2 (PID, PDESC, STATUS) Values (1, 'DVV', 1);
Insert into TEMP2 (PID, PDESC, STATUS) Values (2, 'RMP', 1);
Insert into TEMP2 (PID, PDESC, STATUS) Values (3, 'JPD', 0);
Insert into TEMP2 (PID, PDESC, STATUS) Values (4, 'CDP', 1);
Insert into TEMP2 (PID, PDESC, STATUS) Values (5, 'VMP', 1);
select a.pid,a.quantity,b.pdesc,b.status,sum(sum(a.quantity)) over(order by a.pid) cumu_qty
from temp1 a left join temp2 b on b.pid = a.pid
group by a.pid,a.quantity,b.pdesc,b.status;
PID QUANTITY PDESC STATUS CUMU_QTY
---------- ---------- ---------- ---------- ----------
1 100 DVV 1 100
2 200 RMP 1 300
3 300 JPD 0 600
4 400 CDP 1 1000
5 500 VMP 1 1500
Till this it is ok. But i want to skip the PID:3 (where status is 0) and still the sum(sum)) to run on. Desired result will be look like below.
PID QUANTITY PDESC STATUS CUMU_QTY
---------- ---------- ---------- ---------- ----------
1 100 DVV 1 100
2 200 RMP 1 300
3 300 JPD 0 0
4 400 CDP 1 700
5 500 VMP 1 1200
One option is, it can be done via repeating queries and using UNION. But I'm searching for the way, can it be done via use of analytic function.
Please help/suggest the way out.
Thanks & Regards
Jimit
|
|
|
Re: How to bypass the selective rows from SQL Analytic function [message #658697 is a reply to message #658689] |
Fri, 23 December 2016 05:24 |
|
Michel Cadot
Messages: 68625 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
1/ Note that as you put all columns (but quantity) in your GROUP BY clause the inner SUM(quantity) is useless as it applies to only one row each time and can be replaced by QUANTITY itself.
2/ Just use DECODE/CASE in your "cumu_qty" expression, something like:
decode(status, 0,0, sum(decode(status, 0,0, quantity)) over...)
[Edit: missing letters]
[Updated on: Fri, 23 December 2016 10:01] Report message to a moderator
|
|
|
Re: How to bypass the selective rows from SQL Analytic function [message #658702 is a reply to message #658697] |
Fri, 23 December 2016 07:35 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Just demonstrating what Michel said:
SCOTT@orcl_12.1.0.2.0> select a.pid, a.quantity, b.pdesc, b.status,
2 decode
3 (b.status,
4 0, 0,
5 sum (decode (b.status, 0, 0, a.quantity)) over (order by a.pid)) cumu_qty
6 from temp1 a left join temp2 b on b.pid = a.pid
7 /
PID QUANTITY PDESC STATUS CUMU_QTY
---------- ---------- ---------- ---------- ----------
1 100 DVV 1 100
2 200 RMP 1 300
3 300 JPD 0 0
4 400 CDP 1 700
5 500 VMP 1 1200
5 rows selected.
|
|
|
Re: How to bypass the selective rows from SQL Analytic function [message #658727 is a reply to message #658702] |
Sat, 24 December 2016 08:36 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Or:
SQL> select a.pid,
2 a.quantity,
3 b.pdesc,
4 b.status,
5 b.status * sum(b.status * a.quantity) over(order by a.pid) cumu_qty
6 from temp1 a
7 left join
8 temp2 b
9 on b.pid = a.pid
10 /
PID QUANTITY PDESC STATUS CUMU_QTY
---------- ---------- ---------- ---------- ----------
1 100 DVV 1 100
2 200 RMP 1 300
3 300 JPD 0 0
4 400 CDP 1 700
5 500 VMP 1 1200
SQL>
SY.
|
|
|
Re: How to bypass the selective rows from SQL Analytic function [message #658728 is a reply to message #658727] |
Sat, 24 December 2016 08:41 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Or:
SQL> select a.pid,
2 a.quantity,
3 b.pdesc,
4 b.status,
5 sum(b.status * a.quantity) over(order by b.status,a.pid) cumu_qty
6 from temp1 a
7 left join
8 temp2 b
9 on b.pid = a.pid
10 order by a.pid
11 /
PID QUANTITY PDESC STATUS CUMU_QTY
---------- ---------- ---------- ---------- ----------
1 100 DVV 1 100
2 200 RMP 1 300
3 300 JPD 0 0
4 400 CDP 1 700
5 500 VMP 1 1200
SQL>
SY.
|
|
|
Goto Forum:
Current Time: Fri Mar 29 08:21:31 CDT 2024
|