HiDayurie Dave HiDayurie Dave - 11 months ago 111
SQL Question

ORA-00918: column ambiguously defined error occurred while selecting last row

I have a SQL script to show the last row returned from a resultset.

SELECT * FROM
(
SELECT
D.DESCRIPTIONID_FK,
D.DAY,
D.TIME,
D.PROFILEID_FK,
E.DESCRIPTIONID,
E.DESCRIPTION,
E.PROFILEID_FK,
E.TONEID_FK,
T.TONEID,
T.TONE_NAME,
T.TONE_FILE
FROM
WA_BT_TBL_DAY D,
WA_BT_TBL_DESCRIPTION E,
WA_BT_TBL_TONE T
WHERE
D.DESCRIPTIONID_FK = E.DESCRIPTIONID AND
E.TONEID_FK = T.TONEID AND
D.DAY = 'Tue' AND
D.TIME <= '11:00'
ORDER BY TIME DESC
)
WHERE ROWNUM = 1


When I'm querying, it shows me an error:


ORA-00918: column ambiguously defined



I am using TOAD for this purpose.

Answer Source

The problem was with the D.PROFILEID_FK and E.PROFILEID_FK as when you try to select all using * they will have same name, please try following:

SELECT * FROM
(
    SELECT
        D.DESCRIPTIONID_FK,
        D.DAY,
        D.TIME,
        D.PROFILEID_FK as D_PROFILEID, -- HERE IS WHAT WRONG
        E.DESCRIPTIONID,
        E.DESCRIPTION,
        E.PROFILEID_FK as E_PROFILEID, --AND HERE
        E.TONEID_FK,
        T.TONEID,
        T.TONE_NAME,
        T.TONE_FILE,
        row_number() over(order by d.time desc) as rn
    FROM
        WA_BT_TBL_DAY D,
        WA_BT_TBL_DESCRIPTION E,
        WA_BT_TBL_TONE T
    WHERE
        D.DESCRIPTIONID_FK = E.DESCRIPTIONID AND
        E.TONEID_FK = T.TONEID AND
        D.DAY = 'Tue' AND
        D.TIME <= '11:00'
) 
WHERE rn= 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download