Re: Problem with limitation of result
- From: Beni Buess <beni.buess@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 29 Jun 2004 13:00:17 +0200
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@xxxxxxxxxxxx> 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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
> ____________________________________________________________
> 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: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
