Having a problem with an SQL query.
Query is as follows:
SELECT employee_id,
last_name,
first_name,
TO_DATE('31-DEC-01') - hire_date as "days_with_company",
salary*12 as "annual_salary",
(CASE WHEN "days_with_company" < 720 THEN "annual_salary"*0.1
WHEN "days_with_company" >= 720 AND "days_with_company" < 1440 THEN "annual_salary"*0.2
ELSE "annual_salary"*0.3 END) as "bonus",
("annual_salary" "bonus") as "total_compensation"
FROM employees;
But I get the following error:
ORA-00904: "bonus": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 9 Column: 28
When I look up the error it doesn't appear I'm doing anything wrong!
Please advise.
CodePudding user response:
"bonus" is generated in the same SQ statement in which you are trying to use it. ("annual_salary" "bonus") as "total_compensation" That is not allowed. The following separates it into a separate select statement.
WITH
aset
AS
(SELECT employee_id
, last_name
, first_name
, TO_DATE ('31-DEC-01') - hire_date AS "days_with_company"
, salary * 12 AS "annual_salary"
, (CASE
WHEN "days_with_company" < 720
THEN
"annual_salary" * 0.1
WHEN "days_with_company" >= 720
AND "days_with_company" < 1440
THEN
"annual_salary" * 0.2
ELSE
"annual_salary" * 0.3
END) AS "bonus"
FROM employees)
SELECT aset.*, ("annual_salary" "bonus") AS "total_compensation"
FROM aset;
And I would think twice about enclosing all of the column names in double quotes. Just use Oracle's default. Oracle treats object names as upper case when they are not in double quotes, regardless of case. This is simple, straight forward, and eliminates a lot of agony when mixed case slips in.