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 |
|
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 #681233 is a reply to message #681232] |
Sun, 28 June 2020 02:05 |
|
Michel Cadot
Messages: 68665 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 |
Bill B
Messages: 1971 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);
|
|
|
Goto Forum:
Current Time: Sat Jun 29 08:00:03 CDT 2024
|