Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Problem with limitation of result

Re: Problem with limitation of result

From: Beni Buess <>
Date: Tue, 29 Jun 2004 13:00:17 +0200
Message-ID: <>

thank you! it works now.
i need the between, because i need sometimes something like "BETWEEN 10 AND 20" so i could not do this using "<". but:
i thought, that i've no control over the rowid given by oracle to every row, so i need the inline view to force new rowid's beginning with 1 (or 0) up to the amount of records fetched by the query, because i need this only for limiting the result because my webapp would not be amused getting thousends of records. and if i would not do this using a subquery, i would just get the records with a rowid - given at the time the record was inserted - which is between 0 and 10 for example. and this would not really do what i want.

am i right?

i hope you've understood that, my english is not the best and it was a bit difficult to explain.

Stephane Faroult wrote:

> Beni,
> First your problem : opp.actualamount appears twice in the select list.
> Correctly qualified at your level, but remember that the qualification
> (opp.)is so to say wiped out by a subquery. There must be some basic check
> oneach column name to be unique which makes your select * fail (you are not
> supposed to have two similarly named columns in a table or a view, and the
> rule holds for in-line views, because if you were to refer one of those in
> the WHERE clause, Oracle would be unable to tell which one).
> Second, if you have no ORDER BY in your inline view, then you need no inline
> view. You can write 'and ROWNUM <n' (your BETWEEN is rather unusual, even if
> it should work) in the WHERE clause. But perhaps you kept it for when your
> problem is solved.
> Regards,
> Stephane Faroult
> On Tue, 29 Jun 2004 11:23 , Beni Buess <> sent:
> Hi,
> the following query works as a normal query:
> SELECT sc.seccodedesc AS ACCOUNTOWNER, oppsc.seccodedesc AS
> OPPORTUNITYOWNER, acc.account, ui.username, opp.description AS OFFERNO,
> opp.actualclose, opp.reason, opp.estimatedclose, opp.status,
> opp.actualamount, sod.project_no, opp.actualamount, sod.project_name,
> sod.category, sod.notes, sod.swisslogprob, sod.projectprob,
> ((sod.swisslogprob * sod.projectprob)/100) AS probability,
> sod.targetproject, sod.req_for_pro, sod.handover, sod.createdate
> FROM opportunity opp, swl_opp_desc sod, seccode sc, seccode oppsc,
> account acc, userinfo ui
> WHERE sod.opportunityid = opp.opportunityid
> AND opp.seccodeid = oppsc.seccodeid
> AND acc.accountid = opp.accountid
> AND acc.seccodeid = sc.seccodeid
> AND ui.userid = opp.ACCOUNTMANAGERID
> but doesn't work as a subquery or a view. i
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ:
> ----------------------------------------------------------------
> To unsubscribe send email to:
> put 'unsubscribe' in the subject line.
> --
> Archives are at
> FAQ is at
> -----------------------------------------------------------------
> ____________________________________________________________
> This message has been checked for all known viruses by Messagelabs.

This message may contain legally privileged or confidential information and is therefore addressed to the named persons only. The recipient should inform the sender and delete this message, if he/she is not named as addressee.
The sender disclaims any and all liability for the integrity and punctuality of this message.
The sender has activated an automatic virus scanning by Messagelabs, but does not guarantee the virus free transmission of this message.

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Tue Jun 29 2004 - 05:58:54 CDT

Original text of this message