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 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8790
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2699
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 Go to previous message
Solomon Yakobson
Messages: 2699
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.
Previous Topic: Order By Taking time
Next Topic: ORA-01426 "Numeric Overflow"
Goto Forum:
  


Current Time: Sat Jan 20 19:57:47 CST 2018

Total time taken to generate the page: 0.38699 seconds