Logical file to include only one of many matching records in secondaryu file

Tags:
IBM DB2
RPG
RPGLE
I have a one to many relationship between 2 files. Although there are multiple matching records in the secondary file, the fields I am interested in are common. THerefore i only want one of the secondary records to join to the primary record - avaioding duplicate logical records. I know this could probably be achieved using an SQL query (perhaps a subselect to return unique records from the secondary file and join that record set to the primary) but performance needs to be fast so I don't think this would be appropriate. I just want to get all the data in a logical format if possible rather than do more I/O in the code to look things up. Thanks.

Answer Wiki

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

This sounds like an ideal case for a Join Logical file.

Create a Join Logical file, joining the two files using all the common fields. Define the record format with all the fields that you want to use when you access this Join Logical.

Good Luck!

Discuss This Question: 6  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
  • JustinDouglas
    Thanks ashnirody, I'd already looked at join logicals, but because there is a one to many relationship between my primary and secondary fiels, I would get duplicates of my primary record data. I had a look on the web and all I found related to this was the DDS keyword JDUPSEQ. This keyword deals with the ordering of duplicates rather than removing duplicates which is what I want to do. I'm not sure what I want to do is possible.
    0 pointsBadges:
    report
  • Ashnirody
    You have a one to many relationship between two files and you want to join the two files getting only one particular record from the second file for each record in the primary file. How do you know which record you need from the second file? Either (a). the record in the second file has fields with certain values in which case you can use a join logical with a select/omit OR (b). there are common fields on both files that have the same values in which case you could use a join logical using the common fields to join the two files. If you can send me a DDS of the two files and your requirements for the selection of records I will try to help you.
    100 pointsBadges:
    report
  • SteveCCNJ
    I would stick with SQL personally. The entire machine is SQL under the covers, so it's silly to worry about performance in these cases. I would SELECT with a join from T1 to T2, that chooses the MIN or MAX of the T2 key(s). That would bring back only 1 matching row from T2. You could use CREATE VIEW to store the DDL in the library (similar to a JOIN LOGICAL) or just invoke the SQL at run time.
    0 pointsBadges:
    report
  • SteveKC5F
    If you've got a logical and want one of any of the matching records in the secondary file, why not just do a chain? You'll get one record, and can access the common fields you need.
    0 pointsBadges:
    report
  • JustinDouglas
    I'm running JAVA code agains the database and so have to retrieve a JDBC recordset from the secondary file for each primary reocrd. For now I'll do that. I might then either create an SQL view as mentioned or possibly an RPG stored procedure which would do the chain and return a primary record which included the required field from the secondary file (RPG stored procedure would do the chain). I just wanted to try and have a join logical so that the index was maintained by the operating system and available - rather than executing a query against the DB each time (performace overhead). Performance is important as the data I'm retrieveing is for a client facing web application.
    0 pointsBadges:
    report
  • TomLiotta
    Performance is important as the data I’m retrieveing is for a client facing web application. That's contradictory to the design of the database. If many rows simply duplicate values in multiple columns, it's the database design that's at the root of performance issues. Actually, you haven't said if performance issues exist. You only said that you don't want to run into any. Have you actually tried a SELECT DISTINCT? Tom
    125,585 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