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!

Regards, Bob

In a message dated 20/11/99 6:39:04 AUS Eastern Daylight Time, Peter_Chen@POLK.COM writes:

<< Try adding: AND ROWNUM <= 100 into your where clause.

Peter

>>> <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 FROM HSE 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.

Melanie Barish Sr. Data Analyst IPRO Lake Success, NY >>