Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00904: : invalid identifier (RDBMS 11.2.0.4)
ORA-00904: : invalid identifier [message #681230] Sat, 27 June 2020 19:33 Go to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
I am getting the following error on the CTE can someone take a look

ORA-00904: : invalid identifier

Secondly, I am using a SELECT distinct to generate a list of dates. I have an attendance record for employee_id 3 on O7042020 how can I prevent employees 1,2 from appearing in the list for that day since that date is in the holidays table.

Thanks in advance to all who answer.


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


Create table employees(
 employee_id NUMBER(6), 
 first_name VARCHAR2(20),
 last_name VARCHAR2(20),
 card_num VARCHAR2(10)
);

INSERT into employees(
 employee_id, first_name,
  last_name, card_num)
VALUES 
(1, 'John', 'Doe', 'AAA1');

INSERT into employees(
 employee_id, first_name,
  last_name, card_num)
VALUES 
(2, 'Jane', 'Smith', 'BBB2');

INSERT into employees(
 employee_id, first_name,
  last_name, card_num)
VALUES 
(3, 'Ed', 'Jones', 'CCC3');

CREATE TABLE  emp_attendance        (seq_num NUMBER(10),
  employee_id NUMBER(6),
  start_date DATE,
  end_date DATE,    create_date DATE DEFAULT SYSDATE       );


INSERT into emp_attendance  (seq_num, employee_id,       start_date,       end_date)
VALUES
(1, 1, 

TO_DATE('2020/07/03 15:15:04', 'yyyy/mm/dd hh24:mi:ss'),

TO_DATE('2020/07/03 19:17:34', 'yyyy/mm/dd hh24:mi:ss'));

INSERT into emp_attendance   (seq_num, employee_id,       start_date,       end_date)
VALUES
(2,2, 

TO_DATE('2020/07/03 08:16:34', 'yyyy/mm/dd hh24:mi:ss'),

TO_DATE('2020/07/03 11:11:45', 'yyyy/mm/dd hh24:mi:ss'));


INSERT into emp_attendance   (seq_num, employee_id,       start_date,       end_date)
VALUES
(3,2, 

TO_DATE('2020/07/03 18:17:04', 'yyyy/mm/dd hh24:mi:ss'),

TO_DATE('2020/07/03 21:18:54', 'yyyy/mm/dd hh24:mi:ss'));

INSERT into emp_attendance   (seq_num, employee_id,       start_date,       end_date)
VALUES
(4,3,
TO_DATE('2020/07/04 08:15:00', 'yyyy/mm/dd hh24:mi:ss'),

TO_DATE('2020/07/04 16:19:04', 'yyyy/mm/dd hh24:mi:ss'));


create table holidays(
holiday_date DATE,
holiday_name VARCHAR2(20)
);

INSERT into holidays 
(holiday_date,
holiday_name)
VALUES 
(
TO_DATE('2020/07/04 00:00:00', 'yyyy/mm/dd hh24:mi:ss'),
'July 4th 2020');


With Dates (date)  as (Select distinct trunc(start_date) from emp_attendance )

Select emp.employee_id, dates.date from employees emp cross join Dates
where (emp.employee_id, dates.date) NOT IN
(Select employee_id, trunc(start_date) from emp_attendance);


Re: ORA-00904: : invalid identifier [message #681231 is a reply to message #681230] Sun, 28 June 2020 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

DATE is a reserved word.

Re: ORA-00904: : invalid identifier [message #681232 is a reply to message #681231] Sun, 28 June 2020 01:58 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
That didn't seem to fix the issue.
Would you mind having another look


With date_list (date_range)  as (Select distinct trunc(start_date) from emp_attendance )

Select emp.employee_id,date_list.date_range from employees emp cross join date_list
where (emp.employee_id,date_list.date_range) NOT IN
SELECT employee_id, trunc(start_date) from emp_attendance);

Re: ORA-00904: : invalid identifier [message #681233 is a reply to message #681232] Sun, 28 June 2020 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This was the issue of the previous error:
SQL> With Dates (date)  as (Select distinct trunc(start_date) from emp_attendance )
  2
  3  Select emp.employee_id, dates.date from employees emp cross join Dates
  4  where (emp.employee_id, dates.date) NOT IN
  5  (Select employee_id, trunc(start_date) from emp_attendance);
With Dates (date)  as (Select distinct trunc(start_date) from emp_attendance )
            *
ERROR at line 1:
ORA-00904: : invalid identifier
Now you have another error:
SQL> With date_list (date_range)  as (Select distinct trunc(start_date) from emp_attendance )
  2
  3  Select emp.employee_id,date_list.date_range from employees emp cross join date_list
  4  where (emp.employee_id,date_list.date_range) NOT IN
  5  SELECT employee_id, trunc(start_date) from emp_attendance);
SELECT employee_id, trunc(start_date) from emp_attendance)
*
ERROR at line 5:
ORA-00920: invalid relational operator
Parentheses are missing.

I encourage you to read and search in Database SQL Language Reference before posting this king of beginner errors when you are registered here since almost 10 years.

Re: ORA-00904: : invalid identifier [message #681241 is a reply to message #681233] Mon, 29 June 2020 06:02 Go to previous message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
You with clause is incorrect, use
WITH
    Dates
    AS
        (SELECT DISTINCT TRUNC (Start_date)     date_range
           FROM Emp_attendance)
SELECT Emp.Employee_id, Dates.date_range
  FROM Employees Emp CROSS JOIN Dates
 WHERE (Emp.Employee_id, Dates.date_range) NOT IN
           (SELECT Employee_id, TRUNC (Start_date) FROM Emp_attendance);
Previous Topic: Want to trigger the URL using UTL_HTTP
Next Topic: Problem with Sequence-Creation via Trigger
Goto Forum:
  


Current Time: Mon Sep 28 15:00:17 CDT 2020