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

Question

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;

Marmite Bomber · Accepted Answer · 2019-06-04T04:07:04.517

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.

As per your comment "Simple change the second query to SELECT C.* and add only the required columns from D". But in case we required all the columns from both the tables in that case column name with same name will be there any how and we will be landing to the same error again.. any solution?? — Pranav Sri, Jun 04 '19 at 05:13
@PranavSri as simple as this: for this pair of columns `C.ORIGINATOR = D.ORIGINATOR` you need only one column (in case of an outer join use the column from the inner table). If the table `D` contains e.g. a column `RECIPIENT` which is also in `C` you can add it with an other name: `D.RECIPIENT as D_RECIPIENT` — Marmite Bomber, Jun 04 '19 at 07:00

score 1 · Answer 2 · answered Jun 04 '19 at 06:38

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

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

2 Answers2