Re: Future Effective Dated rows

["Dorman, Tony" <Tony.Dorman@BELLSOUTH.COM>]

Yup.  Typo.

-----Original Message-----
From: John Letourneau [mailto:jml@KSU.EDU]
Sent: Wednesday, May 16, 2001 11:34 AM
To: SQR-USERS@list.iex.net
Subject: Re: Future Effective Dated rows


Should it be Select MIN(G.Effdt) instead of (H.Effdt)?

"Dorman, Tony" wrote:

> Hello,
>
> I am seeking an answer to what I hope will be a simple question.  I am 
>attempting to capture maximum effective dated records that are either less 
>than the system date, or, if there are none, the MINIMUM effective dated row 
>that is future effective dated.  I've included the code that I thought would 
>do the trick, but the runtime is atrocious (my changes are enlarged, and in 
>bold).  What would be the most efficient way to code this?  We are on tools 
>7.55, using an Oracle database.   Any help would be appreciated.  Thanks.
>
> begin-SELECT
> A.EMPLID
> B.EMPL_RCD#
> C.EFFDT
> etc...
> FROM  PS_PERSONAL_DATA A,
>       PS_EMPLOYMENT    B,
>       PS_JOB           C,
>       PS_DISABILITY     PSD,
>       PS_PERS_NID NID
> WHERE A.PER_STATUS = 'E'
>   AND PSD.EMPLID=A.EMPLID
>   AND NID.EMPLID = A.EMPLID
>   AND B.EMPLID     = A.EMPLID
>   AND C.EMPLID     = B.EMPLID
>   AND C.EMPL_RCD#  = B.EMPL_RCD#
>   AND C.EFFDT =
>       ((SELECT MAX(H.EFFDT)
>        FROM   PS_JOB H
>        WHERE  H.EMPLID    = C.EMPLID
>          AND  H.EMPL_RCD# = C.EMPL_RCD#
>          AND  H.EFFDT    <= $AsOfDate)
>          OR
>        SELECT MIN(H.EFFDT)
>        FROM PS_JOB G
>          WHERE  G.EMPLID    = C.EMPLID
>          AND  G.EMPL_RCD# = C.EMPL_RCD#
>          AND  G.EFFDT   >=$AsOfDate))
>   AND C.EFFSEQ =
>       (SELECT MAX(I.EFFSEQ)
>        FROM   PS_JOB I
>        WHERE  I.EMPLID    = C.EMPLID
>          AND  I.EMPL_RCD# = C.EMPL_RCD#
>          AND  I.EFFDT     = C.EFFDT)