Re: how to limit the output?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 27 Oct 1999 10:24:57 -0400
Message-ID: <EgoXOLKXvPiipbCuyC6HyhCtTftL@4ax.com>

A copy of this was sent to kev <kevin.porter_at_fast.no> (if that email address didn't require changing) On Wed, 27 Oct 1999 14:31:50 +0100, you wrote:

>Thomas Kyte wrote:
>
>> A copy of this was sent to kev <kevin.porter_at_fast.no>
>> (if that email address didn't require changing)
>> On Wed, 27 Oct 1999 13:11:10 +0100, you wrote:
>>
>> >Steve McDaniels wrote:
>> >
>> >> select * from
>> >> ( select field1, field2, ... from table_T where <blah blah> order by fieldn
>> >> , etc)
>> >> where rownum <= 3
>> >> /
>> >>
>> >
>> >Have you tried this?
>> >
>> >Here's what happens in 8.0.5 (because an ORDER BY in a view [subquery] is not
>> >allowed):
>> >
>> >SVRMGR> select * from ( select id, title from story order by id ) where rownum
>> >< 4;
>> >select * from ( select id, title from story order by id ) where rownum < 4
>> > *
>> >ORA-00907: missing right parenthesis
>> >
>> >Does this work in any version above 8.0.5?
>> >
>>
>> Yes, this syntax is supported in Oracle8i, release 8.1 and up.
>
>That's great, but before I ask our admin guys to uninstall 8.0.5 and install 8i
>instead (which would be a huge bind), seeing as you're a guru, could you tell me
>definitively what is the best way to emulate this behaviour in 8.0.5 ?
>

just open a cursor and fetch the first N rows, ignoring the rest of the result set will always work.

something like:

tkyte_at_8.0> create or replace type myScalarType as object

  2  (       x int,
  3          y date,
  4          z varchar2(25)

  5 );
  6 /

Type created.

tkyte_at_8.0>
tkyte_at_8.0> create or replace type myTableType as table of myScalarType;   2 /

Type created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> create or replace function getMyTableType return myTableType
  2  as
  3          l_x         myTableType := myTableType();
  4          l_cnt   number := 0;
  5  begin
  6          for x in ( select empno, hiredate, ename from emp order by empno )
  7          loop
  8                  l_cnt := l_cnt+1;
  9                  exit when ( l_cnt > 4 );
 10                  l_x.extend;
 11                  l_x(l_cnt) := myScalarType( x.empno, x.hiredate, x.ename );
 12          end loop;
 13          return l_x;

 14 end;
 15 /

Function created.

tkyte_at_8.0>
tkyte_at_8.0> select *
  2 from THE ( select cast( getMyTableType() as mytableType ) from dual ) a   3 /

         X Y Z

---------- --------- -------------------------
      7369 17-DEC-80 SMITH
      7499 20-FEB-81 ALLEN
      7521 22-FEB-81 WARD
      7566 02-APR-81 JONES

works as well in 8.0 if you have the objects option.

>thanks,
>
>- Kev
>
>>
>>
>> >- Kev
>> >
>> >>
>> >> kev <kevin.porter_at_fast.no> wrote in message
>> >> news:3815ACEB.E5D50D25_at_fast.no...
>> >> > Brian Peasland wrote:
>> >> >
>> >> > > Try:
>> >> > >
>> >> > > SELECT * FROM table WHERE ROWNUM <= 3;
>> >> > >
>> >> >
>> >> > On 8.0.5 this doesn't work if you have an ORDER BY clause, because Oracle
>> >> > works out the rownum before it does the ORDER BY.
>> >> >
>> >> > I've heard that that behaviour has changed in 8.1.5. Can anyone confirm
>> >> > this?
>> >> > I would _really_ like to use rownums and ORDER BYs.
>> >> >
>> >> > thanks,
>> >> >
>> >> > - Kev
>> >> >
>> >> >
>>
>> --
>> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
>> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>>
>> Thomas Kyte tkyte_at_us.oracle.com
>> Oracle Service Industries Reston, VA USA
>>
>> Opinions are mine and do not necessarily reflect those of Oracle Corporation

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Oct 27 1999 - 09:24:57 CDT