IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Get rows based on amount entered from the screen


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2632
Location: NYC,USA

PostPosted: Thu Feb 25, 2016 9:57 pm
Reply with quote

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
View user's profile Send private message
prino
Warnings : 1

Senior Member


Joined: 07 Feb 2009
Posts: 1245
Location: Vilnius, Lithuania

PostPosted: Thu Feb 25, 2016 10:21 pm
Reply with quote

Advice?

You're talking porkies, for 45 NO rows would be returned.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2632
Location: NYC,USA

PostPosted: Thu Feb 25, 2016 10:29 pm
Reply with quote

Advice” is what you give when you “Advise.” icon_biggrin.gif
it should be
Code:
<= 'the entered amount for col2 on screen'
thanks.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Thu Feb 25, 2016 10:55 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2632
Location: NYC,USA

PostPosted: Thu Feb 25, 2016 11:49 pm
Reply with quote

Right and that is managed with seq # column of the same table.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Feb 29, 2016 6:57 pm
Reply with quote

Code:
select a.*
     , (select sum(B.col2) from inp B where b.seq <= a.seq) as running_total
from inp A
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2632
Location: NYC,USA

PostPosted: Mon Feb 29, 2016 9:13 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2632
Location: NYC,USA

PostPosted: Mon Feb 29, 2016 9:42 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2632
Location: NYC,USA

PostPosted: Wed Mar 02, 2016 12:22 am
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Mar 03, 2016 9:50 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2632
Location: NYC,USA

PostPosted: Thu Mar 03, 2016 11:57 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Build two or more rows given some con... DFSORT/ICETOOL 9
No new posts Reading subsequent rows in a query. DB2 12
This topic is locked: you cannot edit posts or make replies. Is there a way to restrict user login... All Other Mainframe Topics 11
No new posts REMOVE DUPLICATE RECORDS BASED ON A S... DFSORT/ICETOOL 4
No new posts Using sort on VB records based on off... DFSORT/ICETOOL 6
Search our Forums:

Back to Top