The concern that I have about the Loops=1 or rowcount 1 is that if you get multiple returns (which those two commands assume), you may not get the "one" you want, even when you ORDER BY. It seems to me (invitation for correction by an SQL guru) that rowcount 1 gets the first row returned by the select, and ORDER BY isn't applied until after the rows are SELECT'd.
Perhaps Loops=1 avoids that by getting all of the rows, then orders them, then only presents the first one(??).
Not being sure of that, I just refine my select to get the specific row, or plan to get the appropriate one after the set of rows SELECT'd have been presented.
HTH,
Bob
-----Original Message-----
From: Les Hancock [mailto:les_hanc@YAHOO.COM]
Sent: Thursday, October 14, 1999 8:41 AM
To: Multiple recipients of list SQR-USERS
Subject: Possible Alternative to Max(EFFDT) and LOOPS=1: ROWNUM = 1
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