printing only the first 10 lines of a query

[erixire]

Hi,

Is there a way to print only the first 10 lines (or another number) if I know that the query will produce over 1000 lines?

Thanks

 

[karluk]

>>you cannot give order by in the sub-query

What version of Oracle are you using? Order by in subqueries was a new feature in Oracle 8.1.6. It works in that and later releases, but fails in earlier releases.

 

[uparna]

as of Oracle 8i , you CAN use order by in subqueries : Syntax:

Select ..... from ... where ... in
(
select ..... where ... order by 1
);
Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at upparna@yahoo.com .

 

[rohanem]

Thats like soo weird!!..We are using 8i..but it shows the error "ORA-00907: missing right parenthesis"

 

[Turkbear]

Hi,
Are you missing it?
( Perhaps some other error like a missing quote is actually what is wrong)
Can you post the code that gives that error?

 

[rohanem]

select * from ads_proc_cntrl where ads_prcsg_dt='15-aug-2002' and proc_nm in(select proc_nm from ads_proc_cntrl where proc_status='COMPLETE' and rownum <11 order by 1)

ERROR at line 3:
ORA-00907: missing right parenthesis

 

[Turkbear]

Ok,
Thats very different than a simple return of first 10 rows
and is not correct to get what it appears you want:
so change

select  * from ads_proc_cntrl where ads_prcsg_dt='15-aug-2002' and proc_nm in(select proc_nm from ads_proc_cntrl where proc_status='COMPLETE' and rownum <11 order by 1)

to

select * from ads_proc_cntrl where ads_prcsg_dt='15-aug-2002' and proc_nm in (select * from (select proc_nm from ads_proc_cntrl where proc_status='COMPLETE' order by 1) where rownum < 11);

I think...

 

[rohanem]

Ya I know..that wanst the query I was using to return the 1st 10 rows..that was just an example of using subquery in 8i..The actual query is as follows


select * from ads_proc_cntrl where ads_prcsg_dt='15-aug-2002'
and rowid in(select rowid from ads_proc_cntrl where ads_prcsg_dt='15-aug-2002'
and rownum <11)

 

[Guest_imported]

Well, I think, the amount of time you have spent backwards and forwards with all these posts, if you would just have thought for a moment...You could have just fetched it from a PL/SQL cursor. I'm sure the amount of time you've spent you could have learned enough PL/SQL to do just that.

Life is short, don't waste it...