Re: regarding loops and effective dates

[Ed Kelly <ed_kelly@HOTMAIL.COM>]

First, if you're using PeopleSoft, and it looks like you are, you probably
want to use the variable $AsOfToday instead of $AsOfDate.  $AsOfToday is
created by the SQC curdttim.sqc and stores the current system date in native
database format.  Second the code you inherited (loops=1) will work, but it
will only retrieve 1 record per select statement.  Does your code have a
main procedure that calls FIND-FLAT-RATE?  I suspect that it does.  This
will cause a second cursor to be created just to retrieve the pay frequency,
units, etc.


If you wanted to join the table PS_FLAT_RATE_TBL directly to something else,
say the job table, you would be forced to used the subquery method.  The
advantage is that only one cursor would need to be built.  There is probably
also a performance savings to this technique, but I cannot say for certain.

Ed Kelly

>From: Kathy Mason <kmason@GIX-GLOBAL.COM>
>Reply-To: SQR-USERS@list.iex.net
>To: Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
>Subject: regarding loops and effective dates
>Date: Tue, 12 Oct 1999 16:39:23 -0500
>
>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

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com