| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Problem with limitation of result
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 <beni.buess_at_swisslog.com> 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: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
> ____________________________________________________________
> This message has been checked for all known viruses by Messagelabs.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Jun 29 2004 - 05:58:54 CDT
 |
 |