Portal | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum 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

Global Moderator


Joined: 21 Sep 2010
Posts: 2484
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: 1220
Location: Vilnius, Lithuania

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

Global Moderator


Joined: 21 Sep 2010
Posts: 2484
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: 2418
Location: Hampshire, 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

Global Moderator


Joined: 21 Sep 2010
Posts: 2484
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

Global Moderator


Joined: 21 Sep 2010
Posts: 2484
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

Global Moderator


Joined: 21 Sep 2010
Posts: 2484
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

Global Moderator


Joined: 21 Sep 2010
Posts: 2484
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

Global Moderator


Joined: 21 Sep 2010
Posts: 2484
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 NOT ABLE TO FIND DB2 ROWS Suehowe DB2 2 Sat Oct 10, 2020 2:05 am
No new posts Need to read duplicate rows from tabl... Poha Eater DB2 2 Wed Oct 07, 2020 11:08 pm
No new posts How to process dependent file based o... HithaSri JCL & VSAM 8 Sun Sep 06, 2020 12:42 pm
This topic is locked: you cannot edit posts or make replies. SORT the records based on the second ... Ramsee SYNCSORT 1 Wed Aug 19, 2020 3:01 pm
No new posts to Insert Rows from File1 to File 2 ... Arun_Tupili DFSORT/ICETOOL 1 Thu Aug 13, 2020 6:22 pm

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