CODEWITHSUNDEEP
sqldatabaseoracle-databaseora-00918
Jung Young-min
Jung Young-min

Reputation: 111

How to fix this ORA-00918 error in this SQL code

I have ORA-00918 error with my code and i could not find the problem... the following code gives me this error.

ORA-00918 : column ambiguously defined

can anyone give me some advice? thanks

SELECT * FROM (
 SELECT * FROM (
  SELECT ROWNUM AS RNUM, A.XML_MSG_ID, A.LOGIN_ID, A.ORIGINATOR, A.RECIPIENT, A.ERROR_CODE, B.DOC_NO, B.DOC_NAME, B.ERROR_MSG
  FROM XML_MANAGE_TBL A, XML_REFERENCE_TBL B
  WHERE A.XML_MGS_ID = B.XML_MSG_ID
   AND A.ERROR_CODE <> '00000000'
   AND A.XML_MSG_ID >= '20190528' AND (SUBSTR(A.XML_MSG_ID, 1, 8)) <= '20190604' ) C, EBILL_USER D WHERE D.COMP_NUM = '1258169573' AND C.ORIGINATOR = D.ORIGINATOR )
 WHERE RNUM BETWEEN CASE WHEN (1-1) != 0 THEN ((1-1)*50)+1 ELSE (1-1)*50 END
 AND 1*50;

Upvotes: 1

Views: 470

Answers (2)

Pranav Sri
Pranav Sri

Reputation: 91

you just need to remove outermost query and use C.RNUM instead of RNUM in where clause. Try with below code:

SELECT * FROM (
  SELECT ROWNUM AS RNUM, A.XML_MSG_ID, A.LOGIN_ID, A.ORIGINATOR, A.RECIPIENT, A.ERROR_CODE, B.DOC_NO, B.DOC_NAME, B.ERROR_MSG
  FROM XML_MANAGE_TBL A, XML_REFERENCE_TBL B
  WHERE A.XML_MGS_ID = B.XML_MSG_ID
   AND A.ERROR_CODE <> '00000000'
   AND A.XML_MSG_ID >= '20190528' AND (SUBSTR(A.XML_MSG_ID, 1, 8)) <= '20190604' ) C, EBILL_USER D WHERE D.COMP_NUM = '1258169573' AND C.ORIGINATOR = D.ORIGINATOR
  and (C.RNUM BETWEEN CASE WHEN (1-1) != 0 THEN ((1-1)*50)+1 ELSE (1-1)*50 END AND 1*50);

Upvotes: 1

Marmite Bomber
Marmite Bomber

Reputation: 21115

The problem is most probably in second subquery select * 

SELECT * FROM (
  ... subquery C ...
) C, EBILL_USER D WHERE ... AND C.ORIGINATOR = D.ORIGINATOR

The table D contains the same columns as the subquery C, for sure the ORIGINATORcolumn

Simple change the second query to SELECT C.* and add only the required columns from D.

The general aproach how to troubleshoot ORA-00918 is to run the query from the innermost subquery and check that the returned column names are unique.

In your case try first, which should be fine

  SELECT ROWNUM AS RNUM, A.XML_MSG_ID, A.LOGIN_ID, A.ORIGINATOR, A.RECIPIENT, A.ERROR_CODE, B.DOC_NO, B.DOC_NAME, B.ERROR_MSG
  FROM XML_MANAGE_TBL A, XML_REFERENCE_TBL B
  WHERE A.XML_MGS_ID = B.XML_MSG_ID
   AND A.ERROR_CODE <> '00000000'
   AND A.XML_MSG_ID >= '20190528' AND (SUBSTR(A.XML_MSG_ID, 1, 8)) <= '20190604'

Than run the second innermost subquery 

SELECT * FROM (
  SELECT ROWNUM AS RNUM, A.XML_MSG_ID, A.LOGIN_ID, A.ORIGINATOR, A.RECIPIENT, A.ERROR_CODE, B.DOC_NO, B.DOC_NAME, B.ERROR_MSG
  FROM XML_MANAGE_TBL A, XML_REFERENCE_TBL B
  WHERE A.XML_MGS_ID = B.XML_MSG_ID
   AND A.ERROR_CODE <> '00000000'
   AND A.XML_MSG_ID >= '20190528' AND (SUBSTR(A.XML_MSG_ID, 1, 8)) <= '20190604' ) C, EBILL_USER D WHERE D.COMP_NUM = '1258169573' AND C.ORIGINATOR = D.ORIGINATOR

In your IDE (e.g. SQL Developer) you will see one and more columns with a suffix _1 which is a sign of duplicated column that must be excluded (for columns from the equijoin predicate) or renamed.

enter image description here

Upvotes: 1

Related Questions