Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Get rows based on amount entered from the screen

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Feb 25, 2016 9:57 pm    Post subject: Get rows based on amount entered from the screen
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

Active Member


Joined: 07 Feb 2009
Posts: 984
Location: Oostende, Belgium

PostPosted: Thu Feb 25, 2016 10:21 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Feb 25, 2016 10:29 pm    Post subject:
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: 1715
Location: UK

PostPosted: Thu Feb 25, 2016 10:55 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Feb 25, 2016 11:49 pm    Post subject:
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: 1278
Location: Belgium

PostPosted: Mon Feb 29, 2016 6:57 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Mon Feb 29, 2016 9:13 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Mon Feb 29, 2016 9:42 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Mar 02, 2016 12:22 am    Post subject:
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: 1278
Location: Belgium

PostPosted: Thu Mar 03, 2016 9:50 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Mar 03, 2016 11:57 pm    Post subject: Reply to: Get rows based on amount entered from the screen
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts IMS DC screen Access sivasaras IMS DB/DC 3 Fri Nov 18, 2016 6:38 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us