Re: Looking for SQL equivalent to RPG -- RPG400-L

Maybe I don't get the problem but will this work?

WITH SimRows (N) AS (
VALUES
*1*,
*2*,
*3*,
*4*,
*5*,
*6*,
*7*,
*8*,
*9*,
*10*,
*11*,
*12*,
*13*,
*14*,
*15*,
*16*,
*17*,
*18*,
*19*,
*20*
)
SELECT *COALESCE*(referr.rfdta, '')
FROM SimRows
*LEFT* OUTER JOIN referr
ON SimRows.N = referr.rfsq2
AND referr.rfsq2 <= *20*
AND referr.rfcat = *:incat*
AND referr.rfslc = *:inslc*
ORDER BY SimRows.N;

--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco

Il giorno mer 18 ott 2023 alle ore 21:14 Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> ha scritto:

Daniel,

Thanks for the help... I did try to paste the records into the original
message, but they appear to have been wrapped.

What I need to accomplish is to account for rows that may NOT exist in the
file for certain select criteria.

In RPG, the array is Char(40) Dim(20), or 800 characters max

The file is
RFCAT, RFSLC, RFSQ2, RFDTA

I need to create a string of RFDTA, in sequence order (RFSQ2 is the
sequence number).

The file might be:
RFCAT RFLSC RFSQ2 RFDTA
'DFAC' '025' 1 Data in sequence 1 up to 40 chars
'DFAC' '025' 2 data in sequence 2
'DFAC' '025' 4 data in sequence 4

In RPG, I SETLL and read into my array, using RFSQ2 as the array index.
The data would fill like this:

Element Data
1 Data in sequence 1 up to 40 chars
2 data in sequence 2
3
4 data in sequence 4

So the resulting string would have 40 blanks between "data in sequence2
" and "data in sequence 4 "

With the SQL LISTAGG statement, I don't have those 40 blanks because
RFSQ2=3 does not exist.
I'm not sure this can be done with SQL.

Greg

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Daniel
Gross
Sent: Tuesday, October 17, 2023 1:22 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: 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.

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