Re: RPG to SQL Help

[Reeve <rfritchman@xxxxxxxxx>]

This code gets the first record.  You can position the read with additional
WHERE clauses.:
select {your columns} from {file}
where key = custno
order by custno, tran_date asc, tran_time asc limit 1;


This code gets the next record from the same customer and with the same
date/greater time or greater-than date.  The program variables :tran_date
and :tran_time contain the results from the first read and we're using them
to SETLL, basically, on the second read.  Really good code would have two
more variables, previous_read_date and previous_read_time, for use in the
second read.
select {your columns} from {file}
where key = custno
and (tran_date = :tran_date and tran_time > :trantime or tran_date >
:tran_date)
order by customer, tran_date asc, tran_time asc limit 1;

READ, SETLL, READE, and READPE were nice...but I'll still take SQL.

If you have duplicate date/time combinations, your results may be
inconsistent.  I'm phasing out a discrete date and a discrete time field
for date/time tracking and going with a timestamp.  Write a few procedure
to extract dates and times, put them in a copy book, and you're good with
accurate timing and sequencing.

Before you ask: there's no zero suppression when you extract a zero date-
or time data-type using an ILE RPG function and MAPVAL is clumsy.  For me,
coding Date_MMDDYY = zzr010_t8a(some_timestamp) is far faster than coding
if's and else's.  Lots of opportunities for creative overloading of
procedures too.

On Mon, Jun 16, 2025 at 9:16 AM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

> This seems so simple... but for some reason I'm at a complete loss.
>
> Given a table as follows:
> CUSTNO, TRAN_DATE, TRAN_TIME, TRAN_TYPE
>
> Using SQL, I need to select a specific record from this file (can use the
> RRN if needed), AND the very next record (chronologically) for the same
> CUSTNO (if one exists).
>
> In RPG, I would just:
> SETLL with the CUSTNO TRAN_DATE TRAN_TIME,  then
> READE with CUSTNO
>
> What is the SQL equivalent to that?
>
>
>
>
> [Logo]<https://www.totalbizfulfillment.com/>    Greg Wilburn
> Director of IT
> 301.895.3792 ext. 1231
> 301.895.3895 direct
> gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
> 1 Corporate Dr
> Grantsville, MD 21536
> www.totalbizfulfillment.com<http://www.totalbizfulfillment.com>
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.