nested loop - most efficient way

[tjsingh]

Hi

I have a program which produces a report. I havea table which stores labour details for different types of people, in my case checkers and printers.

But what i need to achieve is a list of the checkers with their jobs which is fine. But then i need to use the same table using the buffer to get the machine details of where the printer prints to. Bit confusing hey.

Well here is my code:

i wanted to know if there is more efficient way of doing this:

FOR EACH job WHERE
job.kco = 1 AND
job.comp-date ge jp-start AND
job.comp-date le jp-end NO-LOCK,
EACH labour WHERE
labour.kco = 1 AND
labour.kjobcode = job.kjobcode AND
labour.kdeptsn begins "24" AND
(labour.koper = "14") NO-LOCK,
EACH blab where bLab.kco = labour.kco and
bLab.kjobcode begins labour.kjobcode and
(bLab.koper = "14" or
bLab.koper = "9") and
bLab.kdeptsn begins "21" no-lock,
EACH ptjobs WHERE
ptjobs.pt-docket = job.kjobcode :

cheers

 

[TomBascom]

Hi
I have a program which produces a report. I have a table which stores labour details for different types of people, in my case checkers and printers.

But what i need to achieve is a list of the checkers with their jobs which is fine. But then i need to use the same table using the buffer to get the machine details of where the printer prints to. Bit confusing hey.

Well here is my code:

i wanted to know if there is more efficient way of doing this:
cheers

[COLOR=Blue]FOR EACH job WHERE
    job.kco = 1               AND
    job.comp-date ge jp-start AND
    job.comp-date le jp-end   NO-LOCK,
    EACH labour WHERE
         labour.kco = 1                  AND
         labour.kjobcode = job.kjobcode  AND
         labour.kdeptsn begins "24"      AND
         (labour.koper = "14")         NO-LOCK, 
         EACH blab where bLab.kco = labour.kco and
                         bLab.kjobcode begins labour.kjobcode and
                         (bLab.koper = "14" or
                          bLab.koper = "9") and
                         bLab.kdeptsn begins "21" no-lock,
         EACH ptjobs WHERE
              ptjobs.pt-docket = job.kjobcode  :
[/COLOR]

A lot really depends on your indexes and the actual distribution of data in the database.