Oracle DB SQL Query Failing

Question

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.

Brian Leach · Accepted Answer · 2022-04-15 00:07:32Z

"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.

Collectives™ on Stack Overflow

Oracle DB SQL Query Failing

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related