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
saramasaeli
Messages: 5
Registered: September 2008
Junior Member
Hi,
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,
cs03Staff.StaffId,
lms_Staff.Staff_Id,
lms_Staff.Staff_tls_Id,
lms_Student_Class.Staff_Id
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
Littlefoot
Messages: 20901
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: 64140
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

Regards
Michel
Previous Topic: Files names and structure
Next Topic: Function Overloading
Goto Forum:
  


Current Time: Fri Dec 09 10:07:12 CST 2016

Total time taken to generate the page: 0.11852 seconds