CODEWITHSUNDEEP
sqloracle-database
rhys_stubbs
rhys_stubbs

Reputation: 598

Oracle SQL - Column Ambiguously Defined

implementing this query - error of "column ambiguously defined" being thrown. I know it is RE column names and the instances of them being duplicated. Cannot determine which is causing the error.

 SELECT case.case_id,
   case.client_id,
   case.description,
   case.date_filed,
   case.date_closed,
   solicitor.solicitor_id,
   solicitor.first_name,
   solicitor.surname,
   SUM(note.time_spent) total_time_spent
FROM note, case
INNER JOIN note
ON note.case_id = case.case_id
INNER JOIN solicitor
ON solicitor.solicitor_id = note.solicitor_id
GROUP BY note.case_id,
     note.solicitor_id,
     case.client_id,
     case.description,
     case.date_filed,
     case.date_closed
ORDER BY note.case_id,
     case.date_filed;

Upvotes: 1

Views: 540

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

note is in the from clause twice. A simple rule: Never use commas in the from clause. Then, the GROUP BY clause should have all non-aggregaed columns in the FROM clause.

Also, table aliases would make the query easier to write and to read:

SELECT c.case_id, c.client_id, c.description, c.date_filed, c.date_closed,
       s.solicitor_id, s.first_name, s.surname,
       SUM(n.time_spent)as  total_time_spent
FROM case c INNER JOIN
     note n
     ON n.case_id = c.case_id INNER JOIN
     solicitor s
     ON s.solicitor_id = n.solicitor_id
GROUP BY c.case_id, c.client_id, c.description, c.date_filed, c.date_closed,
         s.solicitor_id, s.first_name, s.surname,
ORDER BY c.case_id, c.date_filed;

And, case is a bad name for a table because it is a SQL keyword. It is not an Oracle reserved word, but it still looks awkward.

Upvotes: 2

Md. Shamim Al Mamun
Md. Shamim Al Mamun

Reputation: 386

execute the query

SELECT case.case_id,
   case.client_id,
   case.description,
   case.date_filed,
   case.date_closed,
   solicitor.solicitor_id,
   solicitor.first_name,
   solicitor.surname,
   SUM(note.time_spent) total_time_spent
FROM note, case
INNER JOIN note
ON note.case_id = case.case_id
INNER JOIN solicitor
ON solicitor.solicitor_id = note.solicitor_id
GROUP BY case.case_id,
   case.client_id,
   case.description,
   case.date_filed,
   case.date_closed,
   solicitor.solicitor_id,
   solicitor.first_name,
   solicitor.surname
ORDER BY note.case_id,
     case.date_filed;

I have modified the columns of group by clause.

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116110

I looks like the actual error lies in the fact that you use the note table twice, without aliasing it.

Upvotes: 1

Related Questions