Re: SQL before & after

[rick baird <rick.baird@xxxxxxxxx>]

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.
>
>