how to loop within sql statement without using PL/SQL Block [message #642670] |
Thu, 17 September 2015 00:46 |
ramya29p
Messages: 146 Registered: November 2007 Location: Chennai
|
Senior Member |
|
|
Could any one tell me is it possible to do looping within sql statement.
I have created the table TEST_NET
TEST_NET
ID_No QTY
====================
4120118 100
4120119 50
4120120 120
4120121 -50
4120122 -60
4120122 -10
I need the below output
ID_No QTY Net
=====================================
4120118 100 0
4120119 50 30
4120120 120 120
4120121 -50 0
4120122 -60 0
4120122 -10 0
From TEST_NET table i need to find 1st positive value & 1st negeative value if any and do netting , This should be go on until i have negative value in my table.
From the above sample data,
first positive value is 100 & 1st negative value is -50 so my netting would be 100-50 = 50 , the rest of the records should not be changed & 1st negative value will be marked as 0.
i would be getting the 1st time netting as
1stNet
======
50
50
120
0
-60
-10
Then from this result, 1st positive value is 50 & 1st negative value is -60 so my netting would be 50-60 = -10, i would mark 1st positive value as 0 & 1st negative value as -10.
2nd net
=======
0
50
120
0
-10
-10
This porcess should be continued until i have negative record
[Updated on: Thu, 17 September 2015 00:48] Report message to a moderator
|
|
|
|
Re: how to loop within sql statement without using PL/SQL Block [message #642672 is a reply to message #642671] |
Thu, 17 September 2015 01:01 |
ramya29p
Messages: 146 Registered: November 2007 Location: Chennai
|
Senior Member |
|
|
Please find the below create & Insert Statement
create table test_net (id_no number,qty number);
insert into test_net values(4120118,100);
insert into test_net values(4120119,-50);
insert into test_net values(4120120,120);
insert into test_net values(4120121,-50);
insert into test_net values(4120122,-60);
I have tried the below Query, But with this i could able to get the 1st time netting, I am struck with how to do looping until i have negative record.
SELECT ID_NO, SIGN,QTY,
CASE WHEN ABS(FIRST_NEGATIVE) > FIRST_POSITIVE AND ((QTY=FIRST_POSITIVE) AND RANK=1) THEN QTY-FIRST_POSITIVE
WHEN ABS(FIRST_NEGATIVE) < FIRST_POSITIVE AND ((QTY=FIRST_POSITIVE) AND RANK=1) THEN QTY+FIRST_NEGATIVE
WHEN ABS(FIRST_NEGATIVE) > FIRST_POSITIVE AND ((QTY=FIRST_NEGATIVE)AND RANK=1) THEN QTY+FIRST_POSITIVE
when ABS(FIRST_NEGATIVE) < FIRST_POSITIVE AND ((QTY=FIRST_NEGATIVE)AND RANK=1) THEN 0
else qty
end net
FROM (
SELECT ID_NO ID_NO, SIGN,qty QTY,RANK
,FIRST_VALUE(qty) OVER(ORDER BY SIGN,RANK) FIRST_NEGATIVE
,FIRST_VALUE(qty) OVER(ORDER BY SIGN desc,RANK) FIRST_POSITIVE
from (
SELECT ID_NO,
SIGN(qty) sign,
qty ,
RANK() OVER(PARTITION BY SIGN(qty) ORDER BY ID_NO) AS RANK
FROM TEST_NET)
)
|
|
|
|
|
|
|