2

I'm trying to select multiple rows over different tables but I can't get it to work whatever I do. I get this error:

Error(14,1): PL/SQL: ORA-00918: column ambiguously defined

Code (Note, this is part of a trigger, that's why there's the :new):

SELECT brw.borage, bt.agelower, bt.ageupper
INTO borAge, minAge, maxAge
FROM Borrower brw, BookTitle bt
  INNER JOIN BookCopy bc ON  :new.bcID = bc.bcID
  INNER JOIN BookTitle bt ON bt.isbn = bc.isbn

NOTE: BookTitle has only ONE ageLower and ageUpper column. There's no declerations apart from borAge, minAge and maxAge either.

David
  • 15,652
  • 26
  • 115
  • 156
  • Whay are you doing an `INTO`?, do you want to select the results or insert them on a table? – Lamak Jan 20 '12 at 16:03
  • @Lamak borAge, minAge, maxAge are [probably variables](http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/selectinto_statement.htm#CJAJAAIG). – Conrad Frix Jan 20 '12 at 16:12

2 Answers2

6

Tre problem is that you have two times the sames alias, try this:

SELECT brw.borage, bt.agelower, bt.ageupper
INTO borAge, minAge, maxAge
FROM Borrower brw, BookTitle bt
  INNER JOIN BookCopy bc ON  :new.bcID = bc.bcID
  INNER JOIN BookTitle bt2 ON bt2.isbn = bc.isbn

but this can give you more results then expected, because you put BookTitle table on the FROM part.

I think that only this might be what you really need:

SELECT brw.borage, bt.agelower, bt.ageupper
INTO borAge, minAge, maxAge
FROM Borrower brw
  INNER JOIN BookCopy bc ON  :new.bcID = bc.bcID
  INNER JOIN BookTitle bt ON bt.isbn = bc.isbn
aF.
  • 64,980
  • 43
  • 135
  • 198
  • borAge, minAge and maxAge are variables. Your code compiles, just need to test the trigger now :) – David Jan 20 '12 at 16:22
1

You define BookTitle bt twice.

Scott Hunter
  • 48,888
  • 12
  • 60
  • 101
  • it's the same with bt.ageLower, bookTitle.ageupper . I changed it back to bookTitle.ageLower when I was experimenting :/ – David Jan 20 '12 at 15:54
  • Sorry, I wasn't clear: in your second INNER JOIN, does bt refer to the one defined in the WHERE clause, or the one defined in that JOIN? Change the name of one of them, and use the name of which one you mean. – Scott Hunter Jan 20 '12 at 16:03
  • hmm, I changed the last line to `INNER JOIN bt ON bt.isbn = bc.isbn;` and it says `Table or view does not exist.` – David Jan 20 '12 at 16:12