Home > IT Answers > AS/400 > AS/400: Unique records in file
Help

Question

Asked:
Asked By:
Dec 2 2008   8:39 AM GMT
Ambrish 82   210 pts.

AS/400: Unique records in file


PF/LF, Logical File, Physical File, AS/400 administration

hi all,

i have a problem , suppose i have a physical file which has multiple occurrences of fld1 ,
now i need to access that in distinct (one data single time) order. so i created a logical file with UNIQUE keyword . but when i make query on this LF then msg comes that no member found.
and a dump also occurs that duplicate key not allowed.is thai method is correct to find unique data.

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window
Help

Answer Wiki (Improve, edit or add to this answer)

IMPROVE THIS ANSWER
View History
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
Last Answered: Dec 2 2008  9:42 AM GMT  by Gilly400   23625 pts.




Hi,

You can't create an LF with unique when the data isn't unique. What are you using to access the data? RPG, SQL?

One option is to create a new physical with unique and use CPYF with ERRLVL(*NOMAX)

Regards,

Martin Gilbert.
  • AddThis Social Bookmark Button

Browse more Questions and Answers on AS/400.

Looking for relevant AS/400 Whitepapers? Visit the Search400.com Research Library.


RSS - Discussions Help

Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

Philpl1jb   15100 pts.  |   Dec 2 2008  2:13PM GMT

A logical with UNIQUE keyword will prevent adding records that violate the rule. It will error when compiled if the file contains duplicte key values.

The method to see one occurance of each varies with the access method.
In QUERY/400 you could use report summary functions on fld1 and other fields that you want to report.

In RPG you could use the Key - something like this

*loval SETTLL myfile
Read myfile
dow not %eof(myfile)
process your record
FLd1 SETGT myfile
Read myfile
enddo

The SETGT will position you at the first record past the key value.

Phil

 

Gilly400   23625 pts.  |   Dec 2 2008  3:01PM GMT

Hi,

Or if you’re using SQL you could use a SELECT DISTINCT.

Regards,

Martin Gilbert.

 

Philpl1jb   15100 pts.  |   Dec 2 2008  4:45PM GMT

Select distinct works great provide that all the fields retrivied are the same for a specific value of FLD1. It retrieves distinctly different rows.

 

Ambrish 82   210 pts.  |   Dec 3 2008  4:16AM GMT

thanks , it’s really useful.