Solved

DISTINCT -SQL Function in Logical File Part 2.

Posted on 2006-06-16
13
662 Views
Last Modified: 2008-03-17
http://www.experts-exchange.com/Programming/Programming_Platforms/AS400/Q_21888802.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
0
Comment
Question by:midwestexp
  • 5
  • 5
  • 3
13 Comments
 
LVL 27

Expert Comment

by:tliotta
ID: 16923372
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
0
 
LVL 27

Accepted Solution

by:
tliotta earned 250 total points
ID: 16923442
Ah. I should've read your previous question first...

Consider some RPG code like (pseudo-code)

     PrvKey = *loval

     dou  EOF

         setgt  PrvKey  MyFile
         read   MyFile
                 [process record]
         PrvKey = MyFileKey
     enddo

Start with a low-value key to position at the beginning. Each time you READ a record, process it and use its key to reposition to the beginning of the next key-group with SETGT (Set greater-than PrvKey).

You shouldn't need OPNQRYF with coding like that. (But you should also be able to use OPNQRYF if we get that fixed.)

Tom
0
 

Author Comment

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

Expert Comment

by:daveslater
ID: 16932705
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
0
 
LVL 27

Expert Comment

by:tliotta
ID: 16938842
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
0
 
LVL 14

Expert Comment

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

Expert Comment

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

Author Comment

by:midwestexp
ID: 16942589
It a seconday file

THANKS
MW
0
 
LVL 14

Expert Comment

by:daveslater
ID: 16942717
Hi
what information do you need from the file and does that information exist in all the duplicate records?

Dave
0
 

Author Comment

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

Expert Comment

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

Author Comment

by:midwestexp
ID: 16943082
Thanks Dave/Tom

MW
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
AS/400 Printout WTR status 2 455
IBMi message: Cannot automatically select virtual device 2 157
FTP Server IBM i -  Passive Modus and IP Address 2 44
Monitor SQL Insert 8 39
What does UTC stand for?  “Coordinated Universal Time” – Think of this as the true time on Planet Earth that never changes with the exception of minor leap seconds here and there to account for the changes in the planet's rotation.   What does th…
Fine Tune your automatic Updates for Ubuntu / Debian
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
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 …

932 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

11 Experts available now in Live!

Get 1:1 Help Now