Solved

DISTINCT -SQL Function in Logical File !!!

Posted on 2006-06-16
8
1,087 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:midwestexp
  • 4
  • 4
8 Comments
 

Author Comment

by:midwestexp
ID: 16919990
I have Created a SQL view to Accomplish this but the Only Problem is I am not able to CHAIN /SETLL  to that SQL View, How will I workaround this ? , is there any thing I have to Do in the F-Spec to use SQL view ?
TIA
0
 
LVL 14

Expert Comment

by:daveslater
ID: 16919991
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
0
 

Author Comment

by:midwestexp
ID: 16920019
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 ?

0
 
LVL 14

Expert Comment

by:daveslater
ID: 16920040
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
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:midwestexp
ID: 16920074
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 ?
0
 
LVL 14

Expert Comment

by:daveslater
ID: 16920097
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
0
 
LVL 14

Accepted Solution

by:
daveslater earned 125 total points
ID: 16920152
Hi
opnqryf is the answer - that is what I love about this machine so much history that there are workrounds for anything.

For speed considerations you may need to built an access path over the distinct fields, once that is done the jobs a finished.

for completeness I will put in the required cl.
CL is required as the OPNQRYF command actually opens the file, if the file was not shared then the RPG program would open a new version of the file and all records would be processed not just the distinct values/


PGM

 ovrdbf myfile share(*yes)
opnqryf myfile .........


call pgm

close myfile
dltovr myfile

endpgm

Dave
0
 

Author Comment

by:midwestexp
ID: 16922232
Well opnqryf  Works , As usual, like Dave Comment "I love this System"

Thanks Dave
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
AS400 Printing 10 264
AS400 -  How to dump as/400 & S/36 display ID to file 2 111
JDEdwards AP Batch 4 44
IBM Lotus Notes Rules not working 5 103
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
We need a new way to communicate time sensitive or critical info.   The best part of my role at xMatters is visiting our clients all over the world to learn about how they operate their businesses, share insights that xMatters has gleaned across…
A short film showing how OnPage and Connectwise integration works.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now