Re: RPG to SQL Help

An alternate way: declare a cursor over the customer-date-time ordering,
open it, fetch the first row, fetch the second row, close the cursor.

On Wed, Jun 18, 2025 at 9:02 PM Reeve <rfritchman@xxxxxxxxx> wrote:

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.

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