RE: SQL commands needed to do ROLL-UP and ROLL-DOWN

I think that would be "Fetch Relative nnn" which allows you to specify the
number of rows to move (either forward or backward).  

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: MEovino@xxxxxxxxxxxxxxxxx [mailto:MEovino@xxxxxxxxxxxxxxxxx]
Sent: Wednesday, July 16, 2003 1:02 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL commands needed to do ROLL-UP and ROLL-DOWN



Isn't there a way to determine the row to start the fetch with in SQL?
When you use SQL with Net.Data, you set a variable called START_ROW_NUM to
the row number you want and it works like a champ.  I would think that this
variable is setting something that the SQL CLI uses.

I do this to keep from needing persistence.  I can't say wherther it's the
best way to do what I'm doing, but it works.

Mike E.




 

                    Vern Hamberg

                    <vhamberg@xxxxxxxxxxxxxxxx       To:     Midrange
Systems Technical Discussion                         
                    ology.com>
<midrange-l@xxxxxxxxxxxx>                                            
                    Sent by:                         cc:

                    midrange-l-bounces@xxxxxxx       Subject:     Re: SQL
commands needed to do ROLL-UP and ROLL-DOWN      
                    e.com

 

 

                    07/16/2003 01:16 PM

                    Please respond to Midrange

                    Systems Technical

                    Discussion

 

 





Well, there IS a "fetch first n rows only" clause that came out at least in

V5R1. But I don't see that as the answer.

Isn't there something called persistent CGI? This'd leave the connection
kind of open, with its state preserved. net.data can do this, and that's
basically a CGI thing under the covers, I think.

Are you using embedded SQL? If so, you can decalre the cursor as
scrollable. But the problem still seems related to the state of the
connection - or rather the stateless nature of it. Persisten CGI would
preserve the state.

HTH

Vern

At 12:03 PM 7/16/2003 -0400, you wrote:
>List:
>
>I was looking on the archives but I did not find something similar to my
>situation.  I am working on a CGI that lists manufacturing operations but
it
>takes "juice" from the network.  I am using a SELECT command to do this:
>
>SELECT Mst.ordno, Mst.fitem, Mst.fitwh, Mst.fdesc, Mst.ostat, (Mst.orqty +
>Mst.qtdev), (Mst.orqty + Mst.qtdev - Mst.qtscp - Mst.qtyrc), Mst.qtscp,
>Rtg.opseq, Rtg.opstc, (Mst.orqty + Mst.qtdev - Rtg.tqctd - Mst.qtscp),
>Rtg.scrap, Pls.psstat, Pls.psadd, Pls.psclor, Pls.pstype, Emj.subtp,
>Emj.ostat,
>Mst.ratio, Rtg.awrkc, Rtg.uusamx
>FROM $(AMFLIB).momast Mst
>      left outer join $(AMFLIB).morout Rtg on Mst.ordno = Rtg.ordno
>      left outer join $(COLIB1).planst Pls on Mst.ordno = Pls.psordr and
>Mst.fitem = Pls.psitem
>      left outer join $(PPLDTA).empjob Emj on '$(ENVCD)' = Emj.envcod and
> $(emp)
>= Emj.badge and Mst.ordno = Emj.ordno and Rtg.opseq = Emj.opseq
>WHERE Rtg.awrkc = '$(wkctr)' and Mst.ostat BETWEEN '10' and '40' and
Rtg.opstc
>BETWEEN '10' AND '30' and Emj.badge is Null $(whereclause)
>$(orderby)
>
>I have ROLL-UP and ROLL-DOWN symbols so when the user click on them, the
>corresponding action occurs; however, the whole file (with matching
>records) was
>loaded.  I heard/read that now in 5.2 you can predetermine the number of
>record
>to read in SQL.
>
>Questions:
>1) How I define the specific number of records?
>2) How I must code if PAGEUP or PAGEDOWN is requested? an additional call
>to the
>SQL?
>
>3) Recommendations to improve performance? We have a brand new iSeries
>825(V5.2)
>
>TIA,
>
>Peter Vidal
>Pall Aeropower Corp.
>SR Programmer Analyst
>WWW.Pall.com / 727-539-8448, x2414


_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.





_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.