problem with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

problem with query

Author

Topic

acheo
Starting Member

1 Post

Posted - 2015-02-20 : 12:15:51

Hi,

I have this query: SELECT * FROM(SELECT * FROM AIRCRAFT_USAGE, DD_ISSUE WHERE DD_ISSUE.DI_AIRCRAFT_USAGE_ID = AIRCRAFT_USAGE.AIRCRAFT_USAGE_ID);

which gives me the error:

ORA-00918: column ambiguously defined
00918. 00000 - "column ambiguously defined"
*Cause:
*Action:
Error at Line: 7 Column: 8

I know the SELECT * FROM(SELECT * is not clean but I must keep it as this is a long query built at run-time (not my code).

Any idea how to fix this problem?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-20 : 12:23:46

This is a SQL Server forum not Oracle. However I'd guess that the inner select needs to specify which table columns should come from. e.g.

SELECT * FROM(SELECT DD_ISSUE.DI_AIRCRAFT_USAGE_ID, (other columns) FROM AIRCRAFT_USAGE, DD_ISSUE WHERE DD_ISSUE.DI_AIRCRAFT_USAGE_ID = AIRCRAFT_USAGE.AIRCRAFT_USAGE_ID);

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-02-24 : 04:49:26

The cause is same column name is available in the tables you have used. After a joining same column name duplicates. So that error coming like this.
Better solution is specify the column name instead of *

Regards
Viggneshwar A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources