midwestexp
asked on
DISTINCT -SQL Function in Logical File Part 2.
https://www.experts-exchange.com/questions/21888802/DISTINCT-SQL-Function-in-Logical-File.html
My Question is a Continuation of above Question.
I have Created a CL PGM
PGM
DCL VAR(&PRMASN) TYPE(*CHAR) LEN(8) +
VALUE('XXXXX')
OVRDBF FILE(My FILE) SHARE(*YES)
OPNQRYF FILE((Myliob/myfile)) KEYFLD(*FILE) +
UNIQUEKEY(*ALL)
CALL PGM(mylib/rpgpgm) PARM(&PRMASN)
CLOF OPNID(myfile)
DLTOVR FILE(myfile)
ENDPGM
When I call RPG PGM i am Getting this error msg
Message . . . . : I/O error CPF5180 was detected in file myfile (C G D F).
Cause . . . . . : The RPG procedure EX9003 in program mylib/rpgpgm received
the message CPF5180 at statement 344 while performing I/O operation SETLL on
file myfile. The actual file is mylib/myfile(myfile).
DO i have to do the OPNQRYF in the same RPG or what is the PROBLEM.
TIA
MW
My Question is a Continuation of above Question.
I have Created a CL PGM
PGM
DCL VAR(&PRMASN) TYPE(*CHAR) LEN(8) +
VALUE('XXXXX')
OVRDBF FILE(My FILE) SHARE(*YES)
OPNQRYF FILE((Myliob/myfile)) KEYFLD(*FILE) +
UNIQUEKEY(*ALL)
CALL PGM(mylib/rpgpgm) PARM(&PRMASN)
CLOF OPNID(myfile)
DLTOVR FILE(myfile)
ENDPGM
When I call RPG PGM i am Getting this error msg
Message . . . . : I/O error CPF5180 was detected in file myfile (C G D F).
Cause . . . . . : The RPG procedure EX9003 in program mylib/rpgpgm received
the message CPF5180 at statement 344 while performing I/O operation SETLL on
file myfile. The actual file is mylib/myfile(myfile).
DO i have to do the OPNQRYF in the same RPG or what is the PROBLEM.
TIA
MW
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
F Spec:
flfext02 if e k disk
f rename(extrec:ext02)
Setll Code:
c keyasn setll lfext02
c keyasn reade(e) lfext02
info about the key that is defined for (mylib/myfile):
A R myrec PFILE(myfile)
A K fld1
A K fld2
Yes Tom You are right i can do this By Simple RPG Coding , I would loke to Do this IN OPENQRY.
The Error MSG What i am getting:
Message . . . . : I/O error CPF5180 was detected in file myfile (C G D F).
Cause . . . . . : The RPG procedure EX9003 in program mylib/rpgpgm received
the message CPF5180 at statement 344 while performing I/O operation SETLL on
file myfile. The actual file is mylib/myfile(myfile).
Thanks TOM
MW
flfext02 if e k disk
f rename(extrec:ext02)
Setll Code:
c keyasn setll lfext02
c keyasn reade(e) lfext02
info about the key that is defined for (mylib/myfile):
A R myrec PFILE(myfile)
A K fld1
A K fld2
Yes Tom You are right i can do this By Simple RPG Coding , I would loke to Do this IN OPENQRY.
The Error MSG What i am getting:
Message . . . . : I/O error CPF5180 was detected in file myfile (C G D F).
Cause . . . . . : The RPG procedure EX9003 in program mylib/rpgpgm received
the message CPF5180 at statement 344 while performing I/O operation SETLL on
file myfile. The actual file is mylib/myfile(myfile).
Thanks TOM
MW
Hi
one question, if you only want on record with a key then why are you doing a SETLL and READE.
You can get this record by performing a CHAIN, there is no need to select distinct values.
If what you want is the last record witha duplicate key you have the options of LIFO (Last In First Out) on a logical.
The opnqryf solution assumed that distinct values would be retrieved into the program, but by processing the whole file ie just using a READ with-in a loop.
Dave
one question, if you only want on record with a key then why are you doing a SETLL and READE.
You can get this record by performing a CHAIN, there is no need to select distinct values.
If what you want is the last record witha duplicate key you have the options of LIFO (Last In First Out) on a logical.
The opnqryf solution assumed that distinct values would be retrieved into the program, but by processing the whole file ie just using a READ with-in a loop.
Dave
Dave:
How would you use CHAIN to get to the _next_ key? What version of OS/400 or i5/OS are we working with here?
Tom
How would you use CHAIN to get to the _next_ key? What version of OS/400 or i5/OS are we working with here?
Tom
Hi
the point I was trying to make is:
To get a distinct key (ie an order header) you would use chain, but to get the lines (as there could be many) you use setll and reade.
midwestexp is trying to get a distinct value from a file that duplicates (reason unknown).
The opnqryf option I suggested would allow the program to simply
dou EOF
read MyFile
[process record]
enddo
No need to chain or setll.
The devil is in the detail, until we know what the desired result is it is difficult to provide the best solution.
dave
the point I was trying to make is:
To get a distinct key (ie an order header) you would use chain, but to get the lines (as there could be many) you use setll and reade.
midwestexp is trying to get a distinct value from a file that duplicates (reason unknown).
The opnqryf option I suggested would allow the program to simply
dou EOF
read MyFile
[process record]
enddo
No need to chain or setll.
The devil is in the detail, until we know what the desired result is it is difficult to provide the best solution.
dave
ASKER
Hello Dave/TOM I am sorry for the late replay , I was not working yesterday.
Here what I need to do I have a PF like this , all want to is eliminate the Duplicate value in the second key which is FLD2
it is ok to have Duplicate in FIRST KEY , I am so sorry is I did not explained you the Problem properly
A R myrec PFILE(myfile)
A K fld1
A K fld2
TIA
MW
Here what I need to do I have a PF like this , all want to is eliminate the Duplicate value in the second key which is FLD2
it is ok to have Duplicate in FIRST KEY , I am so sorry is I did not explained you the Problem properly
A R myrec PFILE(myfile)
A K fld1
A K fld2
TIA
MW
Hi
How do you want to process the file?
ie
is it the primary file where you will use the information to access other files
or
is it a seconday file ie you have the value of "fld1" and you want to get some additional information
Dave
How do you want to process the file?
ie
is it the primary file where you will use the information to access other files
or
is it a seconday file ie you have the value of "fld1" and you want to get some additional information
Dave
ASKER
It a seconday file
THANKS
MW
THANKS
MW
Hi
what information do you need from the file and does that information exist in all the duplicate records?
Dave
what information do you need from the file and does that information exist in all the duplicate records?
Dave
ASKER
Hi Dave , I have LF having 2 KEYS , FLD1 and FLD2
both the keys are having the Duplicate values , all I want to do is when I am reading that file from the RPG PGM
I would like to to read With the FLD1 as the key and all I want to see the FLD2 is only unique Values.
TIA
MW
both the keys are having the Duplicate values , all I want to do is when I am reading that file from the RPG PGM
I would like to to read With the FLD1 as the key and all I want to see the FLD2 is only unique Values.
TIA
MW
Hi
you would need to use code (as per what Tom supplied above).
To use the OPNQRYF option you would need to use that as the primary file.
The database will not allow the option of setll / reade on the queried file when summised by unique key.
sometimes we need to program round things
Dave
you would need to use code (as per what Tom supplied above).
To use the OPNQRYF option you would need to use that as the primary file.
The database will not allow the option of setll / reade on the queried file when summised by unique key.
sometimes we need to program round things
Dave
ASKER
Thanks Dave/Tom
MW
MW
Please post the F-spec for the file and at least the relevant I/O statements.
Also, please post info about the key that is defined for (mylib/myfile).
The general sequence is fine. There is something that conflicts between the F-spec, the file key and the I/O.
But, I have to wonder why there is an OPNQRYF being used. It _looks_ as if you have a keyed file that allows non-unique keys and you're trying to use OPNQRYF to filter records and only present you with a single record for each key. If that's not what you're wanting to happen, please describe the result you need.
Tom