Virtual Column in Where clause

Question: Can we call virtual column in the where clause.

Answer: Yes, we can.

Description:

Suppose we have the Standard EMP table (as per oracle Scott user) and want to assign the job title on the basis of SAL column if SAL between 1000 and 3001 then JOB_TITLE is 'JUNIOR' and if SAL between 3001 and 5000 then JOB_TITLE is 'SENIOR' else JOB_TITLE is 'TRAINEE'.

                                                                  Now, we want to fetch the records that have the JOB_TITLE as ‘TRAINEE’ only.  Here JOB_TITLE is the virtual column (this is not physically in EMP table), so we can’t filter directly in WHERE clause.

Ok let’s try,

SELECT EMPNO, ENAME, SAL, DEPTNO,

CASE

 WHEN SAL >=1000  AND SAL<=3000 THEN 'JUNIOR'

 WHEN SAL >=3001  AND SAL<=5000 THEN 'SENIOR'

 ELSE 'TRAINEE'

 END AS JOB_TITLE

FROM EMP

WHERE JOB_TITLE = 'TRAINEE'; 

The above query will return the below ERROR.

Error: Invalid column name 'JOB_TTILE'.

To resolve this Error we can write the same query in different manner.

Method 1

SELECT EMPNO, ENAME, SAL, DEPTNO,

CASE

 WHEN SAL >=1000  AND SAL<=3000 THEN 'JUNIOR'

 WHEN SAL >=3001  AND SAL<=5000 THEN 'SENIOR'

 ELSE 'TRAINEE'

 END AS JOB_TITLE

FROM EMP

WHERE

(CASE

 WHEN SAL >=1000  AND SAL<=3000 THEN 'JUNIOR'

 WHEN SAL >=3001  AND SAL<=5000 THEN 'SENIOR'

 ELSE 'TRAINEE'

 END AS JOB_TITLE) = 'TRAINEE'

Method 2

SELECT EMPNO, ENAME, SAL, DEPTNO, JOB_TITLE

FROM

(

SELECT EMPNO, ENAME, SAL, DEPTNO,

CASE

 WHEN SAL >=5000  AND SAL<=10000 THEN 'JUNIOR'

 WHEN SAL >=10001 AND SAL<=20000 THEN 'SENIOR'

 ELSE 'TRAINEE'

 END AS JOB_TITLE

FROM EMP

)

WHERE JOB_TITLE='TRAINEE'

In the Method 1 we try to take full virtual column in WHERE clause and in Method 2 we try to solve this problem with the IN LINE view.