CODEWITHSUNDEEP
sqloracle-databaseview
user2220653
user2220653

Reputation: 1

Creating views in SQL and receiving error

I know that this is a simple fix, but I can't seem to figure out how to change the code so I don't get this error:

ORA-00918: column ambiguously defined

Here is the code:

CREATE VIEW BOOK_INVENTORY (BRANCH_NUM, UNITS) AS    
SELECT BRANCH_NUM, ON_HAND 
FROM BRANCH, INVENTORY
WHERE BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM
GROUP BY BRANCH.BRANCH_NUM
ORDER BY BRANCH.BRANCH_NUM;

Thanks in advance for the help.

Upvotes: 0

Views: 62

Answers (3)

Mina Tadros
Mina Tadros

Reputation: 634

you are making SELECT BRANCH_NUM, ON_HAND without determine those columns from which tables. May be one of them exists in both tables, modify it for example to

SELECT INVENTORY.BRANCH_NUM, INVENTORY.ON_HAND 
-- ...

of those columns from inside INVENTORY table).

Upvotes: 1

Nick.Mc
Nick.Mc

Reputation: 19225

CREATE VIEW BOOK_INVENTORY (BRANCH_NUM, UNITS) AS    
SELECT BRANCH.BRANCH_NUM, INVENTORY.ON_HAND 
FROM BRANCH, INVENTORY
WHERE BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM
GROUP BY BRANCH.BRANCH_NUM, INVENTORY.ON_HAND 
ORDER BY BRANCH.BRANCH_NUM;

PS this query does not make much sense - why are you grouping? Also you are joining two tables for no apparent reason. The only thing this does is exclude invalid BRANCH's

Upvotes: 0

jpw
jpw

Reputation: 44911

I'm guessing that you need to specify from which table you want BRANCH_NUM like:

CREATE VIEW BOOK_INVENTORY (BRANCH_NUM, UNITS) AS    
SELECT BRANCH.BRANCH_NUM, ON_HAND 
FROM BRANCH, INVENTORY
WHERE BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM
GROUP BY BRANCH.BRANCH_NUM
ORDER BY BRANCH.BRANCH_NUM;

Also, you might want to consider using the post ANSI SQL-92 syntax for joins like so:

CREATE VIEW BOOK_INVENTORY (BRANCH_NUM, UNITS) AS    
SELECT BRANCH.BRANCH_NUM, ON_HAND 
FROM BRANCH
INNER JOIN INVENTORY ON BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM
GROUP BY BRANCH.BRANCH_NUM
ORDER BY BRANCH.BRANCH_NUM;

See [Bad habits to kick : using old-style JOINs][1] for some reasoning about it. [1]: https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins

Upvotes: 1

Related Questions