Ambiguous column on * 1

[travisbrown]

Why would I be getting this error on the query below? Seems like it should just wrap the query and show all fields. I didn't know that * could be ambiguous.

Do I need to define all the fields in the inner query? (which does make it work.)

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Oracle][ODBC][Ora]ORA-00918: column ambiguously defined

/fs/tqts/stats/tqts_query.asp, line 40

SELECT * FROM (
SELECT UP.*, ATT.*, STUD.DMN_ID, ATT.ATTEMPT_DATE, row_number() OVER (PARTITION BY STUD.STUD_ID ORDER BY ATT.ATTEMPT_DATE DESC) RN
FROM PA_USER_LOGIN_ATTEMPT ATT INNER JOIN PA_USER_PRFL UP ON
UP.USER_NAME = ATT.USER_NAME LEFT OUTER JOIN PA_STUDENT STUD ON
UP.USER_NAME = STUD.STUD_ID WHERE UP.USER_NAME NOT IN (
SELECT USER_NAME
FROM PA_USER_LOGIN_ATTEMPT  WHERE ATTEMPT_DATE >
TO_DATE('APRIL 01 2008', 'MM-DD-YYYY')
AND ATTEMPT_SUCCESSFUL = 'Y') AND STUD.DMN_ID NOT IN ('SBU V')
)
WHERE RN = 1

 

[jaxtell]

since some columns have the same name in two tables (USER_NAME), you should explicitly define which columns you want.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[travisbrown]

Ah, I see. Thanks.

SELECT * FROM (SELECT UP.USER_NAME, ATT.USER_NAME...) will cause a conflict.

 

[SantaMufasa]

Travis,

Have you tried your SQL code from within SQL*Plus only (not using ODBC), just to isolate whether the code has issues in a pure SQL*Plus environment? (I, personally, cannot see any ambiguity in your code references.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[travisbrown]

No. I'm not running on a system with SQL*Plus. I only have access ot a DSN for the db, so I wrote a quick little web app to test queries.

I think the ambiguity is happening in cases where * would call like this:

SELECT * FROM (SELECT UP.USER_NAME, ATT.USER_NAME...)

So to fix, I could do something like SELECT * FROM (SELECT UP.USER_NAME UP_USER_NAME, ATT.USER_NAME ATT_USER_NAME...)

In my case, the ambiguity was on DMN_ID, which exists in the UP table and as STUD.DMN_ID

 

[SantaMufasa]

Yep...that would do it.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”