Home » SQL & PL/SQL » SQL & PL/SQL » ora00918
ora00918 [message #271459] Mon, 01 October 2007 12:59 Go to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
I have the following query

SELECT 
  PORTFOLIO_ID, CUSIP, CCMSECTYPE, CCMSECSUBTYPE,  EFF_CV10, MOODY, NAIC, WARF, COMP_ID, 
  COUPON, MATURITY, DISTRESSED_CAT, GAAP_G_L, OAS, COLLATERAL_TYPE, MARKET_YTM,  MARKET_YTW, 
  CPR_12MO_HIST, CPR_12MO_PROJ, CPR_1MO_HIST, CPR_3MO_HIST, CPR_3MO_PROJ, CPR_LIFE_HIST, 
  CPR_LIFE_PROJ,  CPR_1MO_HIST, CPR_1MO_PROJ, CPR_3MO_HIST, CPR_3MO_PROJ, CPR_12MO_HIST, 
  CPR_12MO_PROJ, CPR_LIFE_HIST, CPR_LIFE_PROJ,  ISSUER_ID, ISSUER, EFF_DURATION, EFF_CONVEXITY, 
  EFF_DV01, MARKET_CPR, MARKETVALUE, STATVALUE, GAAPVALUE, PAR, COUNT(1) OVER (PARTITION BY 1) AS TOT_ROWS  
FROM PORTFOLIO_PROFILE_VIEW  
WHERE  
  PORTFOLIO_ID = 'BLAC' AND   RECORD_DATE = TO_DATE('2005-01-31','YYYY-MM-DD')  
  AND CCMSECTYPE = 'CORP'  AND CURRENT_NAIC > 2  AND CURRENT_NAIC  = '3' 
)WHERE  ROWNUM BETWEEN 0 AND 1100



It gives me ORA-00918: column ambiguously defined

If however I take out certain elements from the query the error disappears. How can I get rid of this error. Thanks in advance
Re: ora00918 [message #271465 is a reply to message #271459] Mon, 01 October 2007 13:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Copy and paste SQL*Plus session
2/ SQL*Plus points to the line and column where it found the error
3/ Oracle version is not optional
4/ It is NOT an expert question

Regards
Michel

[Updated on: Mon, 01 October 2007 13:15]

Report message to a moderator

Re: ora00918 [message #271499 is a reply to message #271459] Mon, 01 October 2007 14:21 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You can not use two columns with the same name from two (or more) different table without using table names (or their aliases). See an example:
SQL> select deptno, ename
  2  from emp, dept
  3  where deptno = deptno;
where deptno = deptno
               *
ERROR at line 3:
ORA-00918: column ambiguously defined


SQL> select d.deptno, e.ename
  2  from emp e, dept d
  3  where e.deptno = d.deptno;

    DEPTNO ENAME
---------- ----------
        20 SMITH
        40 ALLEN
        30 WARD
...

If I'm not wrong, there are 6 pairs of columns with the same name (CPR_12MO_HIST, CPR_12MO_PROJ, CPR_3MO_HIST, ...). My example might not be an adequate one (as you don't use more than one table), but this might give you a better picture of the problem:
SQL> select deptno, deptno
  2  from emp, dept
  3  ;
select deptno, deptno
               *
ERROR at line 1:
ORA-00918: column ambiguously defined


SQL> select e.deptno, d.deptno
  2  from emp e, dept d;

    DEPTNO     DEPTNO
---------- ----------
        20         10
        40         10
...


[EDIT: added the second part of the message]

[Updated on: Mon, 01 October 2007 14:27]

Report message to a moderator

Re: ora00918 [message #271501 is a reply to message #271459] Mon, 01 October 2007 14:30 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
And you have too many close parenthesis.
Re: ora00918 [message #271504 is a reply to message #271501] Mon, 01 October 2007 14:37 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd rather say that the first part of the query is missing; see the last WHERE clause? It belongs to something else, not this SELECT statement.
Re: ora00918 [message #271505 is a reply to message #271504] Mon, 01 October 2007 14:39 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Ah, ok, I see. I guess you can say I was giving a literal answer, or something that would happen if you ran the code as posted.
Re: ora00918 [message #271506 is a reply to message #271501] Mon, 01 October 2007 14:39 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
And you have too many close parenthesis.

And this is the reason of his error.
It just post a part of the query, only the subquery which contains several columns of same name.

In addition, doesn't "CURRENT_NAIC > 2 AND CURRENT_NAIC = '3'" sound weird?

Regards
Michel
Previous Topic: Problem with inserting a DATE value (merged & renamed by LF)
Next Topic: MAX .. Group By
Goto Forum:
  


Current Time: Thu Dec 08 18:40:20 CST 2016

Total time taken to generate the page: 0.13010 seconds