Re: regarding loops and effective dates

["Rao, Sam" <Sam_Rao@AFCC.COM>]

Kathy,

The result of both the queries: sub-query to get max(effdt) and LOOPS = 1
sample code below, will be the same. Both will return the most current row
<= $AsOfDate!

LOOPS =1 is an SQR construct, that will get the first row returned by the
query and then exit out of the query, even if the query has more rows to
return.

The sample code below uses LOOPS = 1 to probably improve performance! By
using LOOPS =1 with the Order By Desc, you bypass having to use a correlated
sub-query to find the most current date. But remember, using the ORDER BY
results in a sort operation -- which would again reduce performance! Use the
LOOPS alternative if you have too many sub-queries in your query or the
sub-query runs too slow due to data in the table ,etc,etc...


Sam Rao



> -----Original Message-----
> From: Kathy Mason [SMTP:kmason@GIX-GLOBAL.COM]
> Sent: Tuesday, October 12, 1999 4:39 PM
> To:   Multiple recipients of list SQR-USERS
> Subject:      regarding loops and effective dates
>
> I am not sure if this is an SQR question or a SQL question, but perhaps
> someone
> can clear this up for me.  We have an effective dated system (PeopleSoft)
> on
> Oracle with a HPUX platform.
>
> I was taught to select the max effective date by having something like
> this in
> the where clause:
>
> where l.effdt = (select max(effdt) from ps_flat_rate_tbl
>                             where flat_rate_id = l.flat_rate_id
>                             and effdt <= $AsOfDate)
>
> I have inherited several SQRs that have loops=1 and order by effective
> date in
> desc order (see sample code below).   It appears to produce the same
> results
> either way.  I was just wondering if one way was better than the other or
> is
> there no difference.  Hope you can understand this.  Thanks in Advance.
>
> Kathy
>
> Sample Code:
>
>
> BEGIN-PROCEDURE FIND-FLAT-RATE
> BEGIN-SELECT LOOPS=1
> L.TOTAL_COVRG_RATE
> L.FLAT_RATE_ID
> L.PAY_FREQUENCY
> L.RATE_UNIT
>
>  FROM PS_FLAT_RATE_TBL L
> WHERE L.FLAT_RATE_ID = &K.RATE_TBL_ID
>   AND L.EFFDT        <= $AsOfDate
> ORDER BY L.EFFDT DESC
> END-SELECT
> END-PROCEDURE