Re: SQL before & after

[rick baird <rick.baird@xxxxxxxxx>]

after further review, the first sql statement might retrieve more than
one record, so make it a cursor and fetch them (one at a time, or into
an array).

then loop through the second part getting the 3 records for each myRRN
you select.



On 2/6/06, rick baird <rick.baird@xxxxxxxxx> wrote:
> Try something like this:  (syntax may not be correct)
>
> SELECT RRN(INBDTA) INTO :myRRN
>  FROM G3X1DTA/INBDTA
>  WHERE IQDBSQ > 56000
>  and IQDDTA like '%QTY%%%%-%'
>
> C       eval forRRN = myRRN - 3
> C       for   myRRN to forRRN by -1
>
> SELECT IQDBSQ, IQDREC, IQDDTA into :DataStructurearray
> FROM G3X1DTA/INBDTA
>  WHERE RRN(INBDTA) = :myRRN)
>
> .......process record
>
> C      endfor
>
>
>
>
> On 2/6/06, Henza, Guy <GHenza@xxxxxxxxx> wrote:
> > Rob, Walden,
> >
> > I'm sure that I'm sure that was the example I was playing with a year
> > ago (unless I'm getting senile in my middle age).
> >
> > I've got an inbound EDI file (80 char records).  I want to select the
> > records that contain credits with a negative quantity and get the 3
> > records prior to that record.  The select statement below works but any
> > thing I put after the prior makes it fail.
> >
> > SELECT IQDBSQ, IQDREC, IQDDTA
> >  FROM G3X1DTA/INBDTA prior
> >  WHERE IQDBSQ > 56000
> >   and IQDDTA like '%QTY%%%%-%'
> >
> > Any suggestions?  TIA
> >
> > Guy
> >
> >
> > ------------------------------
> >
> > date: Mon, 6 Feb 2006 14:20:11 -0500
> > from: "Walden H. Leverich" <WaldenL@xxxxxxxxxxxxxxx>
> > subject: RE: SQL before & after
> >
> > >YES! That's it; do you have any working examples?
> > >I've been fussing with it for an hour and can't get the syntax correct.
> >
> >
> > Keep in mind fetch next/prior is _different_ from readp and read in RPG
> > with a SETLL. It sounds to me like you're thinking of using a sql
> > statement like "select fld1 from customer where id = 99" and expecting
> > fetch next to get you customer 100 and fetch prior to get you customer
> > 98, correct? That's not how it will work. On the select you limited the
> > result set to rows where customer=99. Fetch next/prior will get you the
> > next/prior row in the result set, but it won't read off the end of the
> > result set. Think of it as REDPE and READE.
> >
> > -Walden
> >
> > ------------
> > Walden H Leverich III
> > Tech Software
> > (516) 627-3800 x3051
> > WaldenL@xxxxxxxxxxxxxxx
> > http://www.TechSoftInc.com
> >
> > Quiquid latine dictum sit altum viditur.
> > (Whatever is said in Latin seems profound.)
> >
> >
> >
> > ------------------------------
> >
> > --
> > 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.
> >
> >
>