Home » SQL & PL/SQL » SQL & PL/SQL » how to loop within sql statement without using PL/SQL Block
how to loop within sql statement without using PL/SQL Block [message #642670] Thu, 17 September 2015 00:46 Go to next message
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 #642671 is a reply to message #642670] Thu, 17 September 2015 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Where are the CREATE TABLE and INSERT statements for your test case?
And once again:

Michel Cadot wrote on Sat, 22 December 2012 11:09
Also always post your Oracle version, with 4 decimals.


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 Go to previous messageGo to next message
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)
)


Re: how to loop within sql statement without using PL/SQL Block [message #642673 is a reply to message #642672] Thu, 17 September 2015 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Still have not your Oracle version.

Re: how to loop within sql statement without using PL/SQL Block [message #642674 is a reply to message #642673] Thu, 17 September 2015 01:13 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
I am using Oracle 11 g version 11.2.0
Re: how to loop within sql statement without using PL/SQL Block [message #642812 is a reply to message #642674] Mon, 21 September 2015 00:52 Go to previous messageGo to next message
ramsql
Messages: 4
Registered: August 2015
Location: chennai
Junior Member

declare

cursor c1 is select * from test_net where qty>0;

cursor c2 is select * from test_net where qty<0;


begin

for i in c1 loop
exit when c1%rowcount>1;
dbms_output.put_line(i.qty);
end loop;

for i in c2 loop
exit when c2%rowcount>1;
dbms_output.put_line(i.qty);
end loop;

end;

Thanks
Ram
Re: how to loop within sql statement without using PL/SQL Block [message #642814 is a reply to message #642812] Mon, 21 September 2015 01:21 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition to be completely wrong, this is what you call "without using PL/SQL Block"?

[Updated on: Mon, 21 September 2015 01:23]

Report message to a moderator

Previous Topic: query same table multiple times
Next Topic: merge statement (merged :)
Goto Forum:
  


Current Time: Thu Mar 28 03:47:35 CDT 2024