Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 1788
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

Senior Member


Joined: 07 Feb 2009
Posts: 1048
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: 1788
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: 1868
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: 1788
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: 1281
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: 1788
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: 1788
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: 1788
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: 1281
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: 1788
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 Conditional highlighting of rows in I... packerm CLIST & REXX 2 Thu Sep 21, 2017 6:50 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts BUILD OUTFIL based on condition other... balaji81_k DFSORT/ICETOOL 13 Fri Sep 08, 2017 11:06 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts How do you create Eclipse based dialogs jasorn IBM Tools 0 Thu Aug 03, 2017 5:05 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us