RE: Problem with limitation of result

you are using aliases in your subquery -- that's good.
however, the problem is that you select "opp.actualamount" twice.
this is fine for an end result on screen, but not for a view definition.
add another alias or two and you are fine ...
 
Kind regards,
Lex.
 
---------------------------------------------
visit my website at http://www.naturaljoin.nl
---------------------------------------------
 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Beni Buess
Sent: Tuesday, June 29, 2004 10:24
To: oracle-l@xxxxxxxxxxxxx
Subject: Problem with limitation of result


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


-- Binary/unsupported file stripped by Ecartis --
-- Type: text/x-vcard
-- File: Lex de Haan.vcf


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