Problem with limitation of result

From: Beni Buess <beni.buess_at_swisslog.com>
Date: Tue, 29 Jun 2004 11:23:43 +0200
Message-ID: <40E1351F.4080605@swisslog.com>

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 should have this as a subquery, because i want to limit the result using ROWNUM. so i've made the following:

SELECT ROWNUM, oppview.* FROM

(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) oppview

WHERE ROWNUM BETWEEN 0 AND 10 then i get "ORA-00918: column ambiguously defined".

would be great if somebody could explain me what causes that error and how to do that the right way.

thanks
beni

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_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
-----------------------------------------------------------------

Received on Tue Jun 29 2004 - 04:22:35 CDT