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.