Why bother do you want to use OPNQRYF it is an outdated technology!
Why not using embedded SQL in this case he has to even to care about a logical file?
If I understand correctly he wants only the first row of a group of rows with the same key.
First, the easiest RPG solutions would be to use SETGT to position on the next block of data (someone else already mentioned it)
SETLL (KeyFld1: KeyFld2 ...: KeyFldM KeyFldN) YourLF
DoU 1=0;
ReadE (KeyFld1: KeyFld2: ... KeyFldM) YourLF;
If %EOF(YourFile);
Leave;
EndIf;
//Do whatever you want to do with the record
SetGT (KeyFld1: KeyFld2: ... KeyFldM, KeyFldN) YourLF;
EndDo;
An SQL Solution would be:
Exec SQL Declare CsrC01 Cursor for
With x as (Select Row_Number() Over(Partition By KeyFld1, KeyFld2, ... KeyFldM Order By KeyFldN) RowNo,
a.*
from YourTable a
Where ....)
Select *
from x
Where RowNo = 1
Order By ..;
Exec SQL Open CsrC01;
DoU 1=0;
Fetch Next From CsrC01 into :YourDS;
If SQLCODE = 100 or SQLCODE < *Zeros;
Leave;
EndIf;
//Do Whatever you want to do
EndDo;
Exec SQL Close CsrC01;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Mark Waterbury
Sent: Freitag, 26. Juni 2020 16:45
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Emulating SQL "SELECT DISTINCT" with LF?
Patrik,
I believe you can accomplish all of that by using OPNQRYF. :-)
Mark S. Waterbury
On Friday, June 26, 2020, 7:52:39 AM EDT, Patrik Schindler <poc@xxxxxxxxxx> wrote:
Hello,
is it possible with the facilities available through logical files to have some flavour of READ of that particular LF return the next record with a non-equal key? Like the opposite of READE, maybe?
Or must I solve that programmatically (roughly):
SETLL to *LOVAL,
Loop:
READE until EOF,
READ
if EOF, continue
goto Loop.
Also, I dimly remember there were some "group facilities" available with the good old RPG cycle (which even I want to omit). I can't recall to have read about something helpful in the RPGLE Docs.
:wq! PoC
PGP-Key: DDD3 4ABF 6413 38DE -
https://www.pocnet.net/poc-key.asc
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
