CODEWITHSUNDEEP
sqloracle-databaseselectora-00904
Stanly T
Stanly T

Reputation: 1054

How to use temporary column's name in WHERE clause?

First of all, I have to say that I had researched my question before came here and saw a similar question here and all its answers, but unfortunately it hasn't helped me.

Oracle SQL.

I have :

SELECT 
    first_name, 
    salary_dollars, 
    0.85 * salary_dollars AS salary_euro
FROM 
    employees 
WHERE 
    0.85 * salary_dollars > 4000;

Where the salary_euro is my temporary column. I want to avoid double computation in WHERE clause, but if i write WHERE salary_euro > 4000 in my request then I get this error:

ORA-00904: "SALARY_EURO": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 6 Column: 7

Upvotes: 2

Views: 1188

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270823

Oracle now supports lateral joins. This allows you to move the definitions to the FROM clause:

SELECT e.first_name, e.salary_dollars, x.salary_euro    
FROM employees e CROSS JOIN LATERAL
     (SELECT 0.85 * e.salary_dollars AS salary_euro FROM DUAL
     ) x
WHERE x.salary_euro > 4000;

Lateral joins make it simpler to define chains of calculations, because the next expression can depend on the previous one.

Of course, the traditional methods of using a CTE or subquery are also quite viable.

Upvotes: 2

Mureinik
Mureinik

Reputation: 312116

One way around this restriction is to use a subquery to create your temporary columns:

SELECT * 
FROM   (SELECT first_name, 
               salary_dollars, 
               0.85 * salary_dollars AS salary_euro
       FROM    employees) e
WHERE  salary_euro > 4000;

Upvotes: 4

Related Questions