Retrieving unique RPG fields

770 pts.
Tags:
AS/400
I have patients name in Master File.In the master file there is not Key Field and there is no Unique also.In the Master File there are the names of patients,time,date etc. Now i want to retrieve the name of the Patients but when i pull the records from File the name shud not repeat. How to do this..?

Software/Hardware used:
AS400

Answer Wiki

Thanks. We'll let you know when a new response is added.

SELECT DISTINCT &NAME from &FILE
&NAME = field name for Patient Name
&FILE = your file name

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • ankit2002
    Is it possible that we can do this in RPG Program using RPG opcodes.....?
    770 pointsBadges:
    report
  • philpl1jb
    Yes, if the field is keyed by name With native RPG .. Here is the general concept a read loop - if Name <> HldName - Do stuff and set HldName = Name A setgt might be added in the If structure to skip subsequent reads of the same name. Post your code and we'll help. Phil
    49,590 pointsBadges:
    report
  • TomLiotta
    Is it possible that we can do this in RPG Program using RPG opcodes…..? Is it possible to do what? If you're asking if it's possible to do a SQL SELECT with RPG op-codes, then no. You have to use embedded SQL. If you're asking if it's possible to read records and only choose one of each group of names, then yes. But it might be difficult depending on what that means. If it means processing a file and creating a list of distinct names, then you'll need to create an array of names. When you read a record, check to see if the name is already in the array. If it is, then read the next record. If it isn't, then add the name to the array before reading the next record. You might have trouble defining the array since you won't know how many elements need to be declared. If it means reading through the file to find the first record that matches a name that is supplied as a parm to the program or as an input value from a display file, then you just read in a loop until you find a match. You might need to start at the beginning of the file for every name. It'd be better if you kept track in order to know if you needed to start from earlier in the file or if you could read from the current position. As Phil indicates, having an index on the name would reduce the programming to a minimum. One possible alternative if you can't use embedded SQL but still want to execute SQL statements would be to call SQL CLI APIs. These provide functions the same as ODBC and can be used by any ILE language. The SQL CLI APIs can be used without needing the SQL development kit. Tom
    125,585 pointsBadges:
    report
  • CharlieBrowne
    If you must do it with RPG code and no SQL statements and you cannot create a LF, then the array technique Tom suggested would work. What I might fo first is use the FMTDTA command. This would allow you to sort the data in name sequence and the read the file and do a level break on that field.
    41,370 pointsBadges:
    report
  • PSMurray
    This does not sound like a Master File. Master files have a UNIQUE relation. I will not go into the DB Normalization rant. But, you must be aware of this situation as you process the records. > names of patients,time,date etc. Phil MIS
    530 pointsBadges:
    report
  • TomLiotta
    Unfortunately, a Patient Master is unlikely to be unique on name. Names simply aren't unique. It's even possible for the same patient to have multiple Patient IDs. Even so, it's hard to come up with a reason not to have an index on name (first and last). I might even add a Soundex column. Tom
    125,585 pointsBadges:
    report
  • ankit2002
    [...] Retrieving unique RPG fields [...]
    0 pointsBadges:
    report
  • ankit2002
    [...] Ankit2002 wonders how to pull some unique AS/400 fields when the database is in less than ideal shape, and a host of users help [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following