× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. October 2023

Re: Looking for SQL equivalent to RPG

Hi Greg,

so I think I wrapped my head around your problem. And your idea that you posted the other day was good.

I would create a table with all sequence numbers and empty/blank data elements and then do a full outer join.

select coalesce(rftable.rfdata, dummy.rfdata)
from rftable
full outer join dummy on dummy.rfcat = rftable.rfcat
and dummy.rflsc = rftable.rflsc
and dummy.rfsq2 = rftable.rfsq2

This would select all records from RFTABLE (that's how I called your table) and all records from DUMMY (this is the table with the empty records). It matches using the keys and COALESCE will select the first NON-NULL element - to priority has your data table - only if no matching row exists there, the empty dummy row is selected.

Of course you can also create DUMMY on the fly using WITH and VALUES - or as a GLOBAL TEMPORARY TABLE.

HTH
Daniel


Am 18.10.2023 um 21:14 schrieb Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>:

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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.