Re: Simple Question!

In article 2904952214560001_at_dscott.is.net, dscott_at_is.net (David Scott) writes:
>In article <3nio5g$1j7_at_dcsun4.us.oracle.com>, cgohring_at_uk.oracle.com wrote:
>
>> tmaxwell_at_crl.com (Anthony B. Cillo) writes:
>> : I am trying to find the oldest 1000 patients in our database. The
>> : problem is that I cannot get the system to stop after returning 1000
>> : rows. I have tried using rownum but that only works if you are not
>> : using order by.
>> :
>> : Any ideas?
>> :
>> Use a PL/SQL block
>>
>>
>The solution is easier than you think:
>
>CREATE VIEW OLDEST_PATIENTS as
> SELECT patient_name,age
> FROM patient_info
> ORDER BY age DESC
>/
>SELECT * from OLDEST_PATIENTS
> WHERE rownum <= 1000
>/

If I remember right, you cannot have a WHERE clause in a view or a subquery.

If you're writing a PL/SQL routine to fetch the data, you can define the SELECT STATEMENT as a cursor and fetch until the cursor's %ROWCOUNT attribute equals 1000.

Later,
TT Received on Wed May 03 1995 - 00:00:00 CEST