|Date: ||Sun, 21 Nov 1999 18:14:20 EST|
|Sender: ||"SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>|
|Subject: ||Re: Limiting observations with SQL|
|Content-Type: ||text/plain; charset="us-ascii"|
Peter, Nancy and Melanie,
Peter came up with the quick easy answer. I didn't send the rownum statement
because it attaches the rownum before the order by. Realizing later that
Melanie just wanted 100 obs, this is the way to go.
Nancy, you were correct. I thought Oracle passed the info to SAS as it got
them, not after it has them all. My suggestion would make the complete
Oracle program run, then taking the first 100 observations. Definitely the
long way around.
Melanie, I'm sorry for giving you the incorrect answer. I hope you ran it
over the weekend or realized it was taking too long and killed it. I
especially didn't want to give bum info to a fellow New Yorker!
In a message dated 20/11/99 6:39:04 AUS Eastern Daylight Time,
<< Try adding:
AND ROWNUM <= 100
into your where clause.
>>> <MelliJ@AOL.COM> 11/19/99 11:05AM >>>
Hello all - I am trying to limit the number of observations from an SQL
download via an Oracle connection. I am connecting to a very large database,
using a fairly complicated query. I would like to test the code by pulling
the first 100 observations or so. This way, I can verify that it works
without having it run for hours. This is a sample of what my code looks like:
SELECT HSP_ID as MPN,
BENE_CLM_NUM as HIC,
HSE_CLM_FROM_DT as from_dt,
HSE_CLM_THRU_DT as thru_dt,
HSE_CLM_STUS_CD as STATUS,
ADDED_TO_FILE_DATE as ADDED
WHERE HSE_CLM_THRU_DT >= '10/01/1998' AND
HSE_INPAT_OUTPAT_IND = 'I' AND
HSP_ID like '330%' AND
HSE_CLM_STUS_CD not in (33,34,35,36,37,38,39)
ORDER BY HSP_ID,BENE_CLM_NUM,HSE_CLM_FROM_DT,)
Is there any syntax that will allow me to select how many records are pulled
out of Oracle?
Thanks in advance for any assistance.
Sr. Data Analyst
Lake Success, NY