Using 'case expression column' in where clause

Question

SELECT ename
  ,    job
  ,    CASE deptno
         WHEN 10
           THEN 'ACCOUNTS'
         WHEN 20
           THEN 'SALES'
         ELSE 'UNKNOWN'
       END AS department
FROM emp /* !!! */ 
WHERE department = 'SALES'

This fails:

ORA-00904: "%s: invalid identifier"

Is there a way to overcome this limitation in Oracle 10.2 SQL ? How to use the 'case expression column' in where clause ?

The correct answer is the one of Martin Schapendonk. The problem is not in your `case`. — Giann, Jul 01 '11 at 09:01

score 61 · Answer 1 · edited May 23 '17 at 12:09

61

The reason for this error is that SQL SELECT statements are logically * processed in the following order:

FROM: selection of one table or many JOINed ones and all rows combinations that match the ON conditions.
WHERE: conditions are evaluated and rows that do not match are removed.
GROUP BY: rows are grouped (and every group collapses to one row)
HAVING: conditions are evaluated and rows that do not match are removed.
SELECT: list of columns is evaluated.
DISTINCT: duplicate rows are removed (if it's a SELECT DISTINCT statement)
UNION, EXCEPT, INTERSECT: the action of that operand is taken upon the rows of sub-SELECT statements. For example, if it's a UNION, all rows are gathered (and duplicates eliminated unless it's a UNION ALL) after all sub-SELECT statements are evaluated. Accordingly for the EXCEPT or INTERSECT cases.
ORDER BY: rows are ordered.

Therefore, you can't use in WHERE clause, something that hasn't been populated or calculated yet. See also this question: oracle-sql-clause-evaluation-order

^{* logically processed:} Note that database engines may as well choose another order of evaluation for a query (and that's what they usually do!) The only restriction is that the results should be the same as if the above order was used.

Solution is to enclose the query in another one:

SELECT *
FROM
  ( SELECT ename
         , job
         , CASE deptno
             WHEN 10 THEN 'ACCOUNTS'
             WHEN 20 THEN 'SALES'
                     ELSE 'UNKNOWN'
           END AS department
    FROM emp
  ) tmp
WHERE department = 'SALES' ;

or to duplicate the calculation in the WHERE condition:

SELECT ename
     , job
     , CASE deptno
         WHEN 10 THEN 'ACCOUNTS'
         WHEN 20 THEN 'SALES'
                 ELSE 'UNKNOWN'
       END AS department
FROM emp
WHERE
    CASE deptno
      WHEN 10 THEN 'ACCOUNTS'
      WHEN 20 THEN 'SALES'
              ELSE 'UNKNOWN'
    END = 'SALES' ;

I guess this is a simplified version of your query or you could use:

SELECT ename
     , job
     , 'SALES' AS department
FROM emp
WHERE deptno = 20 ;

edited May 23 '17 at 12:09

Community

1
1

answered Jul 01 '11 at 08:53

ypercubeᵀᴹ

113,259
19
174
235

yep this works but it is a bit of an overload. It seems this this the only correct way to do the job. Thank you. – EugeneP Jul 01 '11 at 08:58
@Eugene: You could also rewrite the CASE in the WHERE clause. – ypercubeᵀᴹ Jul 01 '11 at 08:59
yes you're right, but I simplified the query to get to the point. In my case it would be hard to rewrite it this way – EugeneP Jul 01 '11 at 09:02
Your answer is the only correct one that solved the problem. cannot vote for it as I'm not logged in, but I choose it as the best. – EugeneP Jul 01 '11 at 09:08
+1 : Only solution here that both maintains the exact logic and encapsulates that logic to avoid replication of code. – MatBailie Jul 01 '11 at 09:27
please check "duplicate the calculation in the WHERE condition" means second query .. it is wrong ... tmp table not have deptno as a column – pratik garg Jul 04 '11 at 10:44
What a stupid, arbitrary restriction. Is it really so hard to make a second pass over the query? – BlueRaja - Danny Pflughoeft Feb 23 '12 at 23:01
@BlueRaja-DannyPflughoeft: Which restriction do you mean? – ypercubeᵀᴹ Feb 24 '12 at 06:50
@ypercube: That you can't use an alias in a `where` statement. Yes, I realize it's because of how the query is processed, but if SQL were better designed from the start, it wouldn't have to be that way. In fact, I really wish we *could* just redesign SQL from the start, to standardize the implementations, and to get rid of all these **incredibly** poorly-designed choices in the language. It would save me and millions of others of developers and DBAs *countless* hours of headbanging and frustration. But of course, it's far too late for that :( – BlueRaja - Danny Pflughoeft Feb 24 '12 at 18:34
@BlueRaja-DannyPflughoeft: I agree, an alias in `WHERE` that could then be used in `HAVING` or `SELECT` would be handy. And many other details (or major characteristics) in the language design. – ypercubeᵀᴹ Feb 24 '12 at 18:40
@BlueRaja-DannyPflughoeft: I suppose you agree with: [**HAVING A Blunderful Time** or Wish You Were WHERE by **Hugh Darwen**](http://www.dcs.warwick.ac.uk/~hugh/TTM/HAVING-A-Blunderful-Time.html) – ypercubeᵀᴹ Feb 24 '12 at 18:52
1

(also asked [here](http://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by#comment64835469_3841804)) Given the parse order, it'd be handy if you could define aliases for calculations/aggregations in the WHERE or GROUP BY clause. Then you could do `SELECT itemName, FirstLetter, Count(itemName) as n FROM table1 GROUP BY itemName, substring(itemName, 1,1) as FirstLetter` {example based on question in previously linked SO thread}. Do any DB engines support this modified syntax? – mpag Aug 02 '16 at 20:39
@mpag I know no DBMS that has such feature. Only MySQL has something similar but in reverse. Aliases defined in the SELECT can be used in some clauses, like in `GROUP BY` and `HAVING`. – ypercubeᵀᴹ Aug 02 '16 at 20:49

score 7 · Answer 2 · answered Jul 01 '11 at 08:59

7

Your table does not contain a column "department" and thus you can not reference it in your where clause. Use deptno instead.

SELECT ename
,      job
,      CASE deptno
          WHEN 10
          THEN 'ACCOUNTS'
          WHEN 20
          THEN 'SALES'
          ELSE 'UNKNOWN'
       END AS department
FROM   emp /* !!! */ where deptno = 20;

answered Jul 01 '11 at 08:59

Martin Schapendonk

12,893
3
19
24

The query result contains this column, and I guess this is the oracle architectural limitation. The cursor contains department column, no matter if it is a virtual field or a database thing. – EugeneP Jul 01 '11 at 09:04
Call it whatever you want - it's the way things work in an Oracle database. – Martin Schapendonk Jul 01 '11 at 09:09
1

@EugeneP: It's not a limitation of Oracle. It's an SQL one. It's this way because `WHERE` conditions are to be evaluated before the `SELECT` columns are populated (or calculated). – ypercubeᵀᴹ Jul 01 '11 at 09:16
Or(which has no meaning to do but) .... WHERE CASE deptno WHEN 10 THEN 'ACCOUNTS' WHEN 20 THEN 'SALES' ELSE 'UNKNOWN' END = 'SALES' – niktrs Jul 01 '11 at 10:10
1

You could use a predicate on `department` with a nested query, e.g. `SELECT * FROM (SELECT ename, job, CASE ... END AS department FROM emp) WHERE department = 'SALES')`. – Dave Costa Jul 01 '11 at 12:24

score 6 · Answer 3 · answered Jul 01 '11 at 09:01

6

This work for me :

SELECT ename, job
FROM   emp 
WHERE CASE WHEN deptno = 10 THEN 'ACCOUNTS'
           WHEN deptno = 20 THEN 'SALES'
           ELSE 'UNKNOWN'  
      END
      = 'SALES'

answered Jul 01 '11 at 09:01

Cyril Gandon

16,830
14
78
122

It's nice way. I was searching and found your solution perfectly worked for me. Thank you friend. – Smile Dec 19 '13 at 13:14

score 1 · Answer 4 · answered Apr 21 '13 at 02:10

1

select emp_.*
from (SELECT ename
  ,    job
  ,    CASE deptno
         WHEN 10
           THEN 'ACCOUNTS'
         WHEN 20
           THEN 'SALES'
         ELSE 'UNKNOWN'
       END AS department
FROM emp /* !!! */ ) emp_ where emp_.department='UNKNOWN';

answered Apr 21 '13 at 02:10

王奕然

3,891
6
41
62

1

I believe this option has been covered by @ypercube. – Andriy M Apr 21 '13 at 03:31

score 0 · Answer 5 · answered Jan 14 '16 at 13:38

Oracle tries to filter the number of records to be scanned from table by going for the where clause first before select that is why your query fails. Moreover, your query would have never returned rows with department - "Accounts or Unknown" because of the filter Department="SALES"

Try below instead, that will be easy to be fetched by Engine :

SELECT ename, job,'SALES' AS department FROM emp WHERE deptno = 20;

score 0 · Answer 6 · answered Jul 01 '11 at 08:55

0

try:

  SQL> SELECT ename
      2  ,      job
      3  ,      CASE
      4            WHEN  deptno = 10
      5            THEN 'ACCOUNTS'
      6            WHEN  deptno = 20
      7            THEN 'SALES'
     12            ELSE 'UNKNOWN'
     13         END AS department
     14  FROM   emp /* !!! */ where department = 'SALES';

answered Jul 01 '11 at 08:55

marchaos

3,316
4
26
35

2

This fails for the same reasons as the OP's attempt : You can't reference the SELECT's column's (`department` in this case) in the WHERE clause. You can only do that in the ORDER BY and HAVING clauses. – MatBailie Jul 01 '11 at 09:26

Using 'case expression column' in where clause

6 Answers6

Linked