Re: Looking for SQL equivalent to RPG

Hi Greg,

I didn't got my head around your data - maybe the formatting in the mail.

Can you make some sort of screenshot of the file?

If you have a problem with a "missing" value in SQL, I find COALESCE and CASE WHEN expressions very helpful.

Both also work in GROUP BY, PARTITION BY and ORDER BY clauses.

I know what you want to do - but I don't have a clue how your data looks. And I would be happy to help if I can.

HTH
Daniel

Am 17.10.2023 um 17:56 schrieb Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>:

I should have clarified.

The RPG code is already in a service program procedure. I call it all over the place.

I was just looking to create the equivalent using SQL access rather than Native RLA.

Sorry for not stating that clearly.

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Buck Calabro
Sent: Tuesday, October 17, 2023 11:15 AM
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: Re: Looking for SQL equivalent to RPG

On 10/17/2023 10:40 AM, Greg Wilburn wrote:
I have an older procedure that reads records within a file to create a character string from one of the fields (RFDTA) in the file. The resulting string is placed into various data structures, so the "position" is important.

-snip-

This positional/unit record requirement might be the first thing to
reconsider. SQL isn't very good at emulating unit record applications.

I created an SQL statement that works only when all sequence numbers exist.
SELECT rfslc, LISTAGG(RFDTA) WITHIN GROUP(ORDER BY RFCAT, RFSLC, RFSQ2) AS RFDATA
FROM REFERRF
WHERE rfcat=:incat and rfslc=:inslc
GROUP BY RFCAT, RFSLC

Is there a way with SQL to accomplish the same thing without having to read a row at a time?

In my opinion, one of the immense powers of our platform is the ability
to wrap RPG code into an SQL stored procedure. I myself would take this
option. If you worry about duplicating code (who doesn't?), consider how
to refactor the RPG into a subprocedure. Put that into a service
program, then both the existing RPG and the new SQL function/procedure
can reuse that single block of logic, and always return the same output
for the same input.

--
--buck

http://wiki.midrange.com
Your updates make it better!


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