Re: Nested begin-select blocks

[*Kandiah Lingan <KLingan@SMTPGW.ARMS.OHIO-STATE.EDU>]

Hi John,
Once I had to do this. First I done the program using nested SELECT.
The program took 2 hours to run. Then I changed to joins, believe it
or not it took only 10 minutes. Here is the tables and where clauses I
used.
Kandiah

FROM PS_JOB     JOB,
         PS_PERSONAL_DATA PERS,
         PS_EMPLOYMENT EMP_tbl,
         PS_OS_JOB_CHILD JOBCHILD_tbl,
         PS_JOB_EARNS_DIST JED_tbl
WHERE( ( JOB.EFFDT = ( SELECT MAX(B2.EFFDT) FROM PS_JOB B2
                                   WHERE B2.EMPLID = JOB.EMPLID
                                     AND B2.EMPL_RCD# = JOB.EMPL_RCD#
                                     AND B2.EFFDT <=  SYSDATE )
        AND JOB.EFFSEQ = ( SELECT MAX(C2.EFFSEQ) FROM PS_JOB C2
                                   WHERE C2.EMPLID = JOB.EMPLID
                                     AND C2.EMPL_RCD# = JOB.EMPL_RCD#
                                     AND C2.EFFDT = JOB.EFFDT )
           )
           OR JOB.EFFDT > SYSDATE
         )
 AND JOB.EMPLID    = PERS.EMPLID ( + )

 AND JOB.EMPLID         = EMP_tbl.EMPLID ( + )
 AND JOB.EMPL_RCD# = EMP_tbl.EMPL_RCD# ( + )

 AND JOB.EMPLID         = JOBCHILD_tbl.EMPLID   ( + )
 AND JOB.EMPL_RCD# = JOBCHILD_tbl.EMPL_RCD# ( + )
 AND JOB.EFFSEQ        = JOBCHILD_tbl.EFFSEQ    ( + )
 AND JOB.EFFDT           = JOBCHILD_tbl.EFFDT ( + )

 AND JOB.EMPLID        = JED_tbl.EMPLID ( + )
 AND JOB.EMPL_RCD# = JED_tbl.EMPL_RCD# ( + )
 AND JOB.EFFDT           = JED_tbl.EFFDT  ( + )
 AND JOB.EFFSEQ        = JED_tbl.EFFSEQ ( + )

ORDER BY JOB.EMPLID, JOB.EMPL_RCD#, JOB.EFFDT, JOB.EFFSEQ







----------
From:  John Milardovic [SMTP:milardj@SX.COM]
Sent:  Thursday, August 05, 1999 2:43 PM
To:  Multiple recipients of list SQR-USERS
Subject:  Nested begin-select blocks

Hi all.

I'm writing a couple of reports that have nested begin-select blocks
i.e..

        begin-select
        job_numbers
                !for each job number call procedure to select job
details
        from
        some_table

The details from each job number will make up individual *.lis files.

I was just wondering if this would cause a performance issue and
whether I
would be better off joining the two select blocks together and
breaking on
job number to print out the individual details.  In terms of
readability and
ease of programming I find the nested route easier to develop.

I would appreciate it if anyone could give me some advice (in regards
to
performance) based on prior experience.

Thanks.
John Milardovic