Calculated values in the Where Clause SQL Server

Calculated values in the Where Clause SQL Server

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 2,000 after 20% increment in their salary.

SQL> select empno,ename,Sal,(Sal+sal*20/100) as "20%" from emp e
    where e."20%">2000;
where e."20%">2000
      *
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

  select  * from (select empno,ename,sal,(sal+sal*20/100) as "20%" from emp) e
  where e."20%">2000

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.empno,ec.ename,ec.sal,ec."20%" from emp e

inner join

(select empno,ename,sal,(sal+sal*20/100) as "20%" from emp) ec

on e.empno=ec.empno

where ec."20%">2000

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

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