Home » SQL & PL/SQL » SQL & PL/SQL » SQL Problem (Oracle 10g, Windows)
SQL Problem [message #346590] Tue, 09 September 2008 01:26 Go to next message
Messages: 5
Registered: September 2008
Junior Member
I need to write a query that gets the data from 4 tables, and shows the Staff Names from CS03STAFF table.
here is the Query. but when i execute it, it has error

SELECT cs03Staff.StaffName,
FROM (lms_Staff
INNER JOIN lms_Student_Class
ON lms_Staff.Staff_Id = lms_Student_Class.Staff_Id
INNER JOIN lms_Staff
ON cs03Staff.StaffId = lms_Staff.Staff_tls_Id
INNER JOIN lms_Classes
ON lms_Student_Class.Class_Id = lms_Classes.Class_Id)
WHERE ((Class_Date > To_date('31-DEC-2007'))
AND Class_Date < To_date('31-JUL-2008'))
GROUP BY cs03Staff.StaffnAm


Error: ORA-00918: column ambiguously defined

I would appreciate if you could help me solve this problem.

Thanks in advance
Re: SQL Problem [message #346595 is a reply to message #346590] Tue, 09 September 2008 01:41 Go to previous messageGo to next message
Messages: 21151
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's a column that exists in more than one table, but is referenced without table name (or its alias). Therefore, Oracle doesn't know which table's column to use.
Re: SQL Problem [message #346599 is a reply to message #346590] Tue, 09 September 2008 01:43 Go to previous message
Michel Cadot
Messages: 65155
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Some columns are ambiguous as in several referenced table (class_date?).

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your screen including line numbers.

In addition, your to_date are wrong:
SQL> select To_date('31-JUL-2008') from dual;
select To_date('31-JUL-2008') from dual
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Previous Topic: Function Overloading
Next Topic: replace column data wserching tab
Goto Forum:

Current Time: Thu Aug 24 00:12:14 CDT 2017

Total time taken to generate the page: 0.09924 seconds