CODEWITHSUNDEEP
sqloracle-databasejoin
Beefstu
Beefstu

Reputation: 857

Oracle JOIN causing Invalid table name

I have a test CASE below where I'm trying to JOIN the schedule table with the schedule_assignment table so I can access the employee_id in my CTE. When I run the query below I get an error. I am probably doing something wrong with the JOIN but can't seem to get it to work.

ORA-00903: invalid table name

Any help would be greatly appreciated. Thanks in advance to all who answer.

ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

     create table schedule_assignment(
       schedule_id number(4),
       schedule_date DATE,
       employee_id NUMBER(6)
  );

   insert into schedule_assignment(
          schedule_id,
          schedule_date,
          employee_id 
        )
       VALUES          (22,
TO_DATE('2021/08/23 00:00:00', 'yyyy/mm/dd hh24:mi:ss'),1);
  
       create table schedule(
       schedule_id NUMBER(4),
       location_id number(4),
       start_date DATE,
       end_date DATE,
          check (start_date=trunc(start_date,'MI')),
        check (end_date=trunc(end_date,'MI'))
      );

   
   insert into schedule(
     schedule_id,
     location_id,
     start_date,
     end_date 
     )
        VALUES     (22,1,TO_DATE('2021/08/23 11:00:00', 'yyyy/mm/dd hh24:mi:ss'),TO_DATE('2021/08/23 11:04:00', 'yyyy/mm/dd hh24:mi:ss'));

   insert into schedule(
        schedule_id,
         location_id,
         start_date,
         end_date 
       )
       VALUES      (22,2,TO_DATE('2021/08/23 11:10:00', 'yyyy/mm/dd hh24:mi:ss'),
TO_DATE('2021/08/23 11:13:00', 'yyyy/mm/dd hh24:mi:ss'));

    insert into schedule(
      schedule_id,
      location_id,
      start_date,
      end_date 
     )
       VALUES        (22,3,TO_DATE('2021/08/23 11:16:00', 'yyyy/mm/dd hh24:mi:ss'),TO_DATE('2021/08/23 11:19:00', 'yyyy/mm/dd hh24:mi:ss'));

   
WITH sch  AS
(

 SELECT 
   schedule_id,
   employee_id, 
   location_id,  
   start_date,
   end_date          
   FROM    schedule s
   JOIN ON
schedule_assignment sa
        s.schedule_id = sa.schedule_id AND     TRUNC(s.start_date) = sa.schedule_date
)
SELECT * from sch;

Upvotes: 0

Views: 198

Answers (1)

Jon Armstrong
Jon Armstrong

Reputation: 4694

You have a couple of issues, so I'll provide the answer here. The JOIN issue and an ambiguous column reference, which requires a table prefix to resolve:

WITH sch AS (
       SELECT s.schedule_id
            , employee_id
            , location_id
            , start_date
            , end_date          
         FROM schedule s
         JOIN schedule_assignment sa
           ON s.schedule_id = sa.schedule_id
          AND TRUNC(s.start_date) = sa.schedule_date
     )
SELECT * from sch
;

Upvotes: 3

Related Questions