sqldatabaseoracle-databaseora-00918

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;
Solution

  • 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