CODEWITHSUNDEEP
sqloracle-database
Rosario
Rosario

Reputation: 3

SQL Oracle Missing keyword

create table EmployeesUK_9035
  2  as
  3  select e.employee_id,e.first_name || e.Last_name "Name",e.Salary,l.City,e.hire_date from employees e join locations l
  4  where e.employee_id in(select e.employee_id from employees e join departments d on(e.department_id=d.department_id) join locations l on(d.location_id=l.location_id) where l.city='London');

Upvotes: 0

Views: 55

Answers (2)

APC
APC

Reputation: 146219

...from employees e join locations l
4 where

You have missed out the ON section of the JOIN clause (in the main query, not the subquery).

This is the sort of bloomer which should be easy to spot. But because your code is all bunched up it's hard to diagnose. Laying code out nicely isn't just some neat-freakery on the part of experienced developers: readability is actually a feature of the code. Like this ...

create table EmployeesUK_9035
as
select e.employee_id,
    e.first_name || e.Last_name "Name",
    e.Salary,
    l.City,
    e.hire_date 
from employees e 
     join locations l

where e.employee_id in (select e.employee_id 
                        from employees e 
                             join departments d 
                                 on (e.department_id=d.department_id) 
                             join locations l 
                                 on (d.location_id=l.location_id) 
                        where l.city = 'London')
    ;

See how easy it is to spot the missing line? You need an ON clause to join EMPLOYEES and LOCATIONS. However,given the join of the subquery you probably also need to include DEPARTMENTS in the main query because there appears to be no join between the two tables. In which case the query might simplify to 

create table EmployeesUK_9035
as
select e.employee_id,
    e.first_name || e.Last_name "Name",
    e.Salary,
    l.City,
    e.hire_date 
from employees e 
     join departments d 
        on (e.department_id=d.department_id) 
    join locations l 
        on (d.location_id=l.location_id) 
where l.city = 'London'
;

Incidentally please don't use double-quotes and mixed-case for column-aliases when creating a table. You will have to use "Name" in double-quotes and the exact same case every time you reference it, which is a pain because Oracle code is generally case insensitive; that is, all Oracle identifiers are in upper-case by default but case doesn't matter provided we don't wrap the identifiers in double-quotes.

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142705

JOIN should have the ON (to show what you're joining those tables on), while yours doesn't.

I set ON 1 = 1, but you should use columns from EMPLOYEES and LOCATIONS tables.

CREATE TABLE EmployeesUK_9035
AS
   SELECT e.employee_id,
          e.first_name || e.Last_name "Name",
          e.Salary,
          l.City,
          e.hire_date
     FROM employees e JOIN locations l 
                      ON 1 = 1                      --> this
    WHERE e.employee_id IN (SELECT e.employee_id
                              FROM employees e
                                   JOIN departments d
                                      ON (e.department_id = d.department_id)
                                   JOIN locations l
                                      ON (d.location_id = l.location_id)
                             WHERE l.city = 'London');

Upvotes: 0

Related Questions