Can I leverage relations in COBOL query?

Hey Kelly.... you will need to do another find for the PAEMPLOYEE table using the example you describe above. In that example, I usually set my code up to find the PAEMPLOYEE entry right after the EMPLOYEE table... especially since you are using the same key values. Jim

Thanks Jim. That's what I did, but I thought I'd check to see if there was a shortcut. I used the PAEMPLOYEE for simplicity in asking the question, but in this case I'm actually doing a FIND-NEXT loop with PRTIME and need to query PAYMASTR for each PRTIME record to get the Deduction Cycle. I just added the 840-FIND-PYMSET1 within the PERFORM loop. As long as I reset the PYM DB-x values from the PRT with each loop it should work just fine, right? [code] MOVE ZZVACA-COMPANY TO DB-COMPANY MOVE ZZVACA-EMPLOYEE TO DB-EMPLOYEE MOVE ZZVACA-FR-DATE TO DB-PER-END-DATE INITIALIZE ZZVACA-LAST-PPE-PROC-DATE PERFORM 850-FIND-NLT-PRTSET3 IF (PRTIME-FOUND) PERFORM UNTIL (PRTIME-NOTFOUND) OR (PRT-COMPANY NOT = ZZVACA-COMPANY) OR (PRT-EMPLOYEE NOT = ZZVACA-EMPLOYEE) OR ((ZZVACA-TO-DATE-NBR NOT = ZEROES) AND (PRT-PER-END-DATE > ZZVACA-TO-DATE-NBR)) RFC 1025329 - 12/11/2014 - Kelly Meade Get the deduction cycle and skip if > 7 in order to skip special payments MOVE PRT-COMPANY TO DB-COMPANY MOVE PRT-EMPLOYEE TO DB-EMPLOYEE MOVE PRT-CHECK-ID TO DB-CHECK-ID PERFORM 840-FIND-PYMSET1 IF (PRT-PER-END-DATE > ZZVACA-LAST-PPE-PROC-DATE) AND (PYM-DED-CYCLE-NBR < 7) MOVE PRT-PER-END-DATE TO ZZVACA-LAST-PPE-PROC-DATE END-IF PERFORM 860-FIND-NEXT-PRTSET3 END-PERFORM END-IF [/code]

OK... Figured it was just and example... I don't really like using the -NLT- and avoid them when I can. When you go after your PAYMASTER, why don't you use index filtering instead. Would be a lot faster as well.

@jaherb: Assuming he's using period end date on his input form, he's going about it in the correct way; if he drove it from PAYMASTR there is not an index that includes PER-END-DATE.

@John... That is the beauty of Index Filtering... the index does not have to have the field defined in it. He can limit his search time by using the index filter where he can add PER-END-DATE.

@jaherb, while I'll agree that index filters might increase performance, they're not a silver bullet, and depending on your architecture and what you're doing, it also might be slower... The way index filters are implemented is that a set of filter fields/values are passed from the COBOL program to the API, which translates them into a WHERE clause that is sent to the database. Where it differs from "traditional" Lawson 4GL is that the filter is done in the database rather than using IF statements in the COBOL. If the fields included in the index filter *really* exist in the index, that's great. But if they don't, the database is essentially using index whatever it can--but has to use a scan when the desired filter isn't part of the index. Just like if you do it directly in a SQL query. Where index filters can help performance is in transfering the data between the database and the COBOL program. While traditional FIND-XXXSET1/NEXT logic transfers the data row-by-row from the database to the API to the COBOL program, the index filter API receives an entire resultset from the database, and then loops thru and returns records row-by-row to the COBOL program. So, you remove round-tripping to the database for long-running loops, and theoretically it will be faster. So, when applied to Woozy's question... Based on his code, he's using a pretty narrow index (company/employee/per-end-date) that already exists on the PRTIME detail table, and using FINDs to access the PAYMASTR header records. So he's already limiting the number of records being retrieved from PRTIME to only those for a given employee and time period, and the APIs are smart enough to cache the PAYMASTR FINDs. Changing that use index filter on PAYMASTR to return only those checks for a particular per end date, and the doing another index filter on PRTIME for the time records associated with each check on that particular date would actually increase the physical number of records read from PAYMASTR, since it would use the index with COMPANY/EMPLOYEE to look for a given employee's payments, but would then have to use a scan on those records to filter by period end date, since that's not in the index. That's where it gets interesting. Even though more physical reads are done, with index filters it would result in fewer round trips to the database. So, the answer is of course, it depends....

Thanks Jim and John. Very interesting information. I do use index filtering occasionally, but it didn't occur to me in this case. I always have to dig up an example, because I can never remember how to do it. For what I'm doing in this particular case, performance isn't an issue, and I was just "adding" the PAYMASTR piece into an existing piece of code so it seemed to be the simplist solution. However, if I'm understanding Jim correctly, my other option would have been to do the PAYMASTR query first using the index filtering, and then do looping targeted finds against PRTIME to pull that detail? I can see that - particularly since PRTIME can be so gigantic. I'll keep that in mind for the future. Thanks again!