Re: Problem with limitation of result

 
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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: