|
View previous topic :: View next topic
|
| Author |
Message |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
I am trying to achieve this through one single query(cursor) in native sp.
Table A,
| Code: |
col1 col2
------ -----
111 33.00
112 11.00
113 10.00 |
User will enter col2 amount from the screen and expecting fir N records >= 'the entered amount for col2 on screen'
so in this case the cursor should return first 2 rows if the amount entered for col2 is 45.
Any advise. |
|
| Back to top |
|
 |
prino
Senior Member
Joined: 07 Feb 2009
Posts: 1306
Location: Vilnius, Lithuania
|
|
|
|
Advice?
You're talking porkies, for 45 NO rows would be returned. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
Advice” is what you give when you “Advise.” 
it should be
| Code: |
| <= 'the entered amount for col2 on screen' |
thanks. |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| Suppose you can get more than one combination of rows to get the toal required - remembering that rows are not necessarily returned in the same sequence every time unless ORDERed. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| Right and that is managed with seq # column of the same table. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
select a.*
, (select sum(B.col2) from inp B where b.seq <= a.seq) as running_total
from inp A
|
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
GuyC, I am not sure how will that work based on my input value unless I am mistaken.
So to say again, If I get $45.00 entered from the screen then I should get first 2 rows from the above table as shown because I wanted all records from the table where (sum(col2) for all the records) <= 'Entered amount($45.00) and the final result set will be ordered by seq# of the same table. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| GuyC, I got what is written. Thanks for that, I will take it further and let you know if I get any issues. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
The Running total helped and wrote the whole query but now query is having a performance issue.
This is how the query becomes if I have to fill my requirement. but I endup getting SQLCODE=-905, SQLSTATE=57014. ASU time limit reached.
| Code: |
select cal_tab.all,
cal_tab.running_total
from
(select a.* as all
, (select sum(B.col2) from inp B where b.seq <= a.seq) as running_total
from inp A ) as cal_tab
where cal_tab.running_total <= 45 |
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
yes, problem is: it will create running_total for the whole table, before you start looking for the ones <= 45.
Another solution is running_total thru a recursive query :
| Code: |
With cte (seq,c1,c2,tot) as (
Select A.seq, A.col1, A.col2, A.col2 as tot from inp A
where A.seq = (select min(B.seq) from inp B)
union all
select C.seq, C.col1, C.col2 , cte.tot + C.col2 from cte, inp C
where C.seq = (select min(D.seq) from inp D where D.seq > cte.seq)
and cte.tot + C.col2 <= 45
)
select * from cte |
But you would still need a index on Seq to be performant. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
Thanks.
Also I tried with one of the OLAP function and it works but MIPS are still high compared to achieving the same through two cursors.
| Code: |
| select sum(sum(col2)) over (order by col1) from table group by col1 |
|
|
| Back to top |
|
|
|
|
