Re: Simple Question!

In article <dscott-0305952205520001_at_dscott.is.net>, dscott_at_is.net (David Scott) writes:
::From: dscott_at_is.net (David Scott)
::Newsgroups: comp.databases.oracle
::Subject: Re: Simple Question!
::
::In article <3o8n84$361_at_theopolis.orl.mmc.com>, troyt_at_sun.com wrote:
::
::> 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.

  I think this was meant to be ORDER BY clause not WHERE clause

::>
::Please review your Concepts Manual, page 5-10; even the very first example
::of a view contains a WHERE clause! Also check your SQL Language Reference
::Manual; the syntax explicitly supports WHERE.
::
::Not only does a view support WHERE; it can use GROUP BY, HAVING, and ORDER BY.
::
::The previous example even allows for multiple people to be the same age.
::Good luck - and check the manuals!
::
::------------------------------------------------------------------
::David Scott
::...how can I keep from singing?
::email: dscott_at_is.net
::------------------------------------------------------------------
::

 A select statement in a view does *not* allow ORDER BY   

 The SQL Reference manual states

  "A view's query can be any SELECT statement without the ORDER BY or    FOR UPDATE clauses"

-- 
+--------------------------+-------------------------+-----------------------+
| Ian Bainbridge           | bainbridge.i_at_bgers.co.uk|Phone: (44)191-216-0202|
| British Gas ERS          | Newcastle Upon Tyne, UK |            x2790      |
+--------------------------+-------------------------+-----------------------+