Re: Possible Alternative to Max(EFFDT) and LOOPS=1: ROWNUM =

[Doug Cummings <dcummings@VICR.COM>]

My understanding is that the rownum as applied by oracle happens as the row is
pulled from the database, before the sort(order by). Given that there is no
guarantee that your logic will work.

Regards,
   Doug Cummings

reference: Oracle8 the Complete Reference, Osborne / McGraw Hill



____________________Reply Separator____________________
Subject:    Possible Alternative to Max(EFFDT) and LOOPS=1:  ROWNUM = 1
Author: Les Hancock <les_hanc@YAHOO.COM>
Date:       10/14/99 5:40 AM

In regards to the recent discussion around SQR's using
the LOOPS = 1 and max(effdt):


Instead of this:

------------------------------------
begin-select
L.TOTAL_COVRG_RATE

from ps_flat_rate_tbl l
where l.RATE_ID = &K.RATE_TBL_ID
  and l.effdt = (select max(effdt) from
                  ps_flat_rate_tbl
                 where flat_rate_id = l.flat_rate_id
                   and effdt <= $AsOfDate)
end-select
------------------------------------


and instead of this:

------------------------------------
begin-select loops = 1

L.TOTAL_COVRG_RATE
from ps_flat_rate_tbl l
where l.RATE_ID = &K.RATE_TBL_ID
  and l.effdt <= $AsOfDate
order by l.effdt desc
end-select
------------------------------------


Does the following always work?  And if so, is it
acceptable/recommended?

------------------------------------
begin-select

L.TOTAL_COVRG_RATE
from ps_flat_rate_tbl l
where l.RATE_ID = &K.RATE_TBL_ID
  and l.effdt <= $AsOfDate
  AND ROWNUM = 1   !<---------??????????????????
order by l.effdt desc

end-select
------------------------------------

__________________________________________________
Do You Yahoo!?
Bid and sell for free at http://auctions.yahoo.com