| 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
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
but doesn't work as a subquery or a view. i
 |
 |