CODEWITHSUNDEEP
sqloracle11g
Mike Warren
Mike Warren

Reputation: 3866

Oracle 11g "Duplicate column name" error on view with no duplicate columns

Somehow, Oracle 11g thinks there's duplicate column names in my view-creation code:

DROP VIEW users_view;
CREATE VIEW users_view AS 
    SELECT * FROM users u
        INNER JOIN states s 
            ON u.state_id = s.state_id
        INNER JOIN accounts a
            ON u.account_id = a.account_id
;
/

I run this in SQLDeveloper and get this error message: 

Error starting at line : 109 in command -
CREATE VIEW users_view AS 
    SELECT * FROM users u
        INNER JOIN states s 
            ON u.state_id = s.state_id
        INNER JOIN accounts a
            ON u.account_id = a.account_id
Error report -
ORA-00957: duplicate column name
00957. 00000 -  "duplicate column name"
*Cause:    
*Action:

Any way I can resolve this?

Upvotes: 0

Views: 2392

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Obviously, this query has duplicate columns:

SELECT *
FROM users u INNER JOIN
     states s 
     ON u.state_id = s.state_id INNER JOIN
     accounts a
     ON u.account_id = a.account_id

Among the duplicates are u.state_id, s.state_id, u.account_id, and a.account_id -- the table alias is ignored.

I recommend that you list all the columns that you want from each table. However, if the duplicates are only the JOIN keys, then there is a short-cut -- you can use USING instead of ON:

SELECT *
FROM users u INNER JOIN
     states s 
     USING (state_id) INNER JOIN
     accounts a
     USING (account_id)

Upvotes: 2

Related Questions