midwestexp
asked on
DISTINCT -SQL Function in Logical File !!!
Is there any Way I can Eliminate the Duplicate Value being read by the PGM .
I have a Physical File with one field having the Duplicate Value . If Possible I want to create a logical file
Which will automatically eliminate the Duplicate Value ( Like DISTINCT -SQL Function) ?
TIA
MW
I have a Physical File with one field having the Duplicate Value . If Possible I want to create a logical file
Which will automatically eliminate the Duplicate Value ( Like DISTINCT -SQL Function) ?
TIA
MW
Hi
There is any easy way using OPNQRYF if that is any good.
OPNQRYF FILE((MYFILE)) KEYFLD((KEY1) (KEY2) (KEY3)) UNIQUEKEY(*ALL)
or if you are using a logical with the correct keys
OPNQRYF FILE((MYFILE)) KEYFLD(*FILE) UNIQUEKEY(*ALL)
Have fun
Dave
There is any easy way using OPNQRYF if that is any good.
OPNQRYF FILE((MYFILE)) KEYFLD((KEY1) (KEY2) (KEY3)) UNIQUEKEY(*ALL)
or if you are using a logical with the correct keys
OPNQRYF FILE((MYFILE)) KEYFLD(*FILE) UNIQUEKEY(*ALL)
Have fun
Dave
ASKER
Dave it is good to see you after a long time (atleast in my question),
OPNQRYF FILE((MYFILE)) KEYFLD((KEY1) (KEY2) (KEY3)) UNIQUEKEY(*ALL)
Will this Work Even if i have Duplicate values in the Physical file ?
OPNQRYF FILE((MYFILE)) KEYFLD((KEY1) (KEY2) (KEY3)) UNIQUEKEY(*ALL)
Will this Work Even if i have Duplicate values in the Physical file ?
if
you want to access it on a setll / read then do it via sql
/exec sql
c+ decare mycursor for
c+ select * from myfile
c+ where field1 = :value1
c+ and field2 = :value2
c+ fetch first 1 rows only
/end-exec
Dave
you want to access it on a setll / read then do it via sql
/exec sql
c+ decare mycursor for
c+ select * from myfile
c+ where field1 = :value1
c+ and field2 = :value2
c+ fetch first 1 rows only
/end-exec
Dave
ASKER
My Problem is for this particular PGM my Company is not alowing me to use SQL, Please Don't ask me Why ?!!!
i know i can do this RPG by haveing anothe Hold-KEY-Field SKip reading of the same Key . I would like to to is there any other Way like DDS, DDM thinkgs like that .
OPNQRYF FILE((MYFILE)) KEYFLD((KEY1) (KEY2) (KEY3)) UNIQUEKEY(*ALL)
Will this Work Even if i have Duplicate values in the Physical file ?
i know i can do this RPG by haveing anothe Hold-KEY-Field SKip reading of the same Key . I would like to to is there any other Way like DDS, DDM thinkgs like that .
OPNQRYF FILE((MYFILE)) KEYFLD((KEY1) (KEY2) (KEY3)) UNIQUEKEY(*ALL)
Will this Work Even if i have Duplicate values in the Physical file ?
Hi
the opnqry will work even if there are duplicates in the physical
enter the opnqryf command on a command line then use the CPYFRMQRYF command to copy the result set to a a file in QTEMP - you can then see what would be brough into the program.
The advantage of this method is you can still access details in the whole record not just the distinct values like in SQL.
if it is just some spacific values you want then craete a logical with the fields in or use embened sql with a
select distinct.
Give a few more details of the program logic and we can reccoment the best way forward.
Dave
the opnqry will work even if there are duplicates in the physical
enter the opnqryf command on a command line then use the CPYFRMQRYF command to copy the result set to a a file in QTEMP - you can then see what would be brough into the program.
The advantage of this method is you can still access details in the whole record not just the distinct values like in SQL.
if it is just some spacific values you want then craete a logical with the fields in or use embened sql with a
select distinct.
Give a few more details of the program logic and we can reccoment the best way forward.
Dave
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well opnqryf Works , As usual, like Dave Comment "I love this System"
Thanks Dave
Thanks Dave
ASKER
TIA