Calculated values in the Where Clause Oracle

Calculated Values are not allowed in where clause, It should be part of inner view or join.

In Hr Schema , Display all employees who is more than 10,000 after 20% increment in their salary.

SQL> select Employee_ID,First_name,Salary,(Salary+salary*20/100) as "20%" from employees
  2  where e."20%">10000;
where e."20%">10000
      *
ERROR at line 2:
ORA-00904: "E"."20%": invalid identifier

In order to solve this. There are 2 methods.

                 1.Using Inner Views
                 2.Using Inner join.

Method 1:Using Inner Views

SQL>  select  * from (select employee_ID,first_name,salary,(salary+salary*20/100) as "20%" from employees) e
  where e."20%">10000

Inner view gives a table called e, using columns in table user can filter in where clause.


Method 2: Join Employees table with Inner View.

select  ec.Employee_ID,ec.first_name,ec.salary,ec."20%",e.last_name from employees e

inner join

(select employee_ID,first_name,salary,(salary+salary*20/100) as "20%" from employees) ec

on e.employee_ID=ec.employee_ID

where ec."20%">10000

This method also produces same results, I prefer method 1, because Table scan is 1.

Tags:Calculated values in the Where Clause Oracle,Filter based on calculated value, compute column in where clause, join table and inner view, inner views in oracle, filter based on pseudo column.