Re: RPG to SQL Help -- RPG400-L

Hi Greg,

you're welcome.

Your idea of a scrolling cursor with this and previous transaction is of course possible.

a) you can JOIN your transactions with the "next" transaction and get both values in one row

b) you can use LAG/LEAD and OLAP window function OVER(ORDER BY ...) to get the current values and the values of the next row

OLAP is the more modern idea - but with many columns it can get tedious. A "Self-JOIN" on the next is more old-fashioned - but you can easily use SELECT TABLE1.*, TABLE2.* which might be easier.

Just try both and select the solution you like.

HTH
Daniel

Am 16.06.2025 um 20:11 schrieb Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>:

Charles/Daniel - thanks!

This seems to work (My date/time fields are numeric).

The users will be viewing the transaction list (like Sean's statement) in a browser. When they select a row, I'm going to show them what values changed between the two transactions (the list of fields per row is much larger than my example).

I guess I was thinking there should be a way to "set the cursor" on my sorted list, then simply "scroll" to the next value.
Maybe I was trying to get too "fancy", lol.

Thanks again
Greg

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles Wilt
Sent: Monday, June 16, 2025 12:38 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: RPG to SQL Help

This is a good start...But as I read the original post, he's got a starting
date & time...

So the where needs to be
where custno = :your_custno
AND ( (tran_date = :your_date AND tran_time >= :your_time )
OR tran_date > :your_date )

Optionally, you could combine tran_date and tran_time into a timestamp,
that gives an easier comparison.
But depending on the number of records, performance could suffer unless you
add a derived index for the combined timestamp column.

Charles

On Mon, Jun 16, 2025 at 10:28 AM Daniel Gross <daniel@xxxxxxxx> wrote:

Hi Greg,

you would simply SELECT the rows you need, and limit it to a maximum of 2
rows.

Example:

dcl-ds rows qualified dim(*auto:2);
custno ...
tran_date ...
tran_time ...
tran_type ...
end-ds;

%elem(rows) = *zero;
exec sql declare csrCustTrans cursor for
select custno, tran_date, tran_time, tran_type
from <your-table>
where custno = :your_custno
order by tran_date, tran_type
limit 2;
exec sql open csrCustTrans;
exec sql fetch csrCustTrans for 2 rows into :rows;

After the FETCH the variable sqlerrd(3) is set to the number of rows
fetched - in your can 0, 1 or 2. But you can also check the number of
elements in the rows data-structure array.

Don't forget to CLOSE the cursor after you have your data.

HTH and kind regards,
Daniel

Am 16.06.2025 um 18:16 schrieb Greg Wilburn <

gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>:

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.

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

Greg Wilburn
Director of IT
301.895.3792 ext. 1231
--
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.