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.
