ORA-00918: column ambiguously defined

Question

SELECT 
    store_seq.NEXTVAL AS STORE_ID,
    DIST_WRK_STR.*
FROM
    (
        SELECT DISTINCT
            WRK_HHT.COUNTRY_ID AS COUNTRY_ID,
            CASE
                WHEN WRK_HHT.LOCAL_STORE_CD IS NULL THEN
                    WRK_HHT.TMP_STORE_CD
                ELSE
                    WRK_HHT.LOCAL_STORE_CD
            END AS LOCAL_STORE_CD,
            CASE
                WHEN WRK_HHT.LOCAL_STORE_CD IS NULL THEN
                    1
                ELSE
                    0
            END AS TMP_SLC_FLG,
            SYSDATE,
            SYSDATE 
        FROM  
            WRK_HHTLOGDATA WRK_HHT
            LEFT OUTER JOIN
                MST_STORE MST_STR
            ON
                WRK_HHT.COUNTRY_ID = MST_STR.COUNTRY_ID
                AND
                MST_STR.STORE_LOCAL_CD =
                (
                    CASE
                        WHEN WRK_HHT.LOCAL_STORE_CD IS NULL THEN
                            WRK_HHT.TMP_STORE_CD
                        ELSE
                            WRK_HHT.LOCAL_STORE_CD
                    END
                )
        WHERE
            MST_STR.STORE_ID IS NULL
            AND
            (
                WRK_HHT.LOCAL_STORE_CD IS NOT NULL
                OR
                WRK_HHT.TMP_STORE_CD IS NOT NULL
            )
            AND
            WRK_HHT.COUNTRY_ID = ncountry_id
            AND
            WRK_HHT.LOAD_ID = nload_id
    ) DIST_WRK_STR

hi all,

i am getting the error as column name ambiguously defined in the place DIST_WRK_STR.*

ncountry_id and nload_id are not qualified. It's probably one of those. — Dan Bracuk, Aug 28 '13 at 16:34
can you list your tables structure? We need to know the columns names to be able to answer your question. But if you qualify all of your fields then you would solve the problem — Jafar Kofahi, Aug 28 '13 at 16:53

score 4 · Answer 1 · answered Aug 28 '13 at 16:44

4

The inner SELECT lists SYSDATE twice in the column list. Since no column alias is provided for either of them, Oracle names both columns "SYSDATE" and generates an error message since it can't differentiate between the two columns.

answered Aug 28 '13 at 16:44

Vadim K.

2,370
18
26

I think Oracle would name them `sysdate` and `sysdate_1`, so this most likely is not the error – Jafar Kofahi Aug 28 '13 at 16:52
2

@JafarKofahi, incorrect. SQL Developer (not Oracle nor SQL*Plus) does this as a convenience when you do something like `select sysdate, sysdate from dual`. However, executing `select foo.* from (select sysdate, sysdate from dual) foo;` produces the `column ambiguously defined` message. – Vadim K. Aug 28 '13 at 17:03
Vadim K: +1 very interesting indeed.. you learn something new every day :) – Jafar Kofahi Aug 28 '13 at 17:05

ORA-00918: column ambiguously defined

1 Answers1