Use a calculated field in the where clause

Question

Is there a way to use a calculated field in the where clause?

I want to do something like

SELECT a, b, a+b as TOTAL FROM (
   select 7 as a, 8 as b FROM DUAL
   UNION ALL
   select 8 as a, 8 as b FROM DUAL
   UNION ALL
   select 0 as a, 0 as b FROM DUAL
)
WHERE TOTAL <> 0
;

but I get ORA-00904: "TOTAL": invalid identifier.

So I have to use

SELECT a, b, a+b as TOTAL FROM (
   select 7 as a, 8 as b FROM DUAL
   UNION ALL
   select 8 as a, 8 as b FROM DUAL
   UNION ALL
   select 0 as a, 0 as b FROM DUAL
)
WHERE a+b <> 0
;

score 46 · Accepted Answer · answered Oct 07 '10 at 19:01

46

Logically, the select clause is one of the last parts of a query evaluated, so the aliases and derived columns are not available. (Except to order by, which logically happens last.)

Using a derived table is away around this:

select * 
from (SELECT a, b, a+b as TOTAL FROM ( 
           select 7 as a, 8 as b FROM DUAL 
           UNION ALL 
           select 8 as a, 8 as b FROM DUAL 
           UNION ALL 
           select 0 as a, 0 as b FROM DUAL) 
    )
WHERE TOTAL <> 0 
;

answered Oct 07 '10 at 19:01

Shannon Severance

18,025
3
46
67

1

I was trying this in a Stored Procedure and the above syntax would not work unless I aliased the derived table as well. – DilbertDave Mar 09 '12 at 15:00
@DilbertDave, interesting. Were you trying in Oracle? If I remember correctly, SQL Server always required derived tables to be aliased. – Shannon Severance Mar 09 '12 at 15:32
3

OP was asking about Oracle which doesn't need the alias. I'm glad this answer was able to help you even though it was for a different platform. – Shannon Severance Mar 09 '12 at 19:07
2

Ah - just noticed the Oracle error message and tags *blush*. Still, it pointed me in the right direction so upvote :-) – DilbertDave Mar 14 '12 at 08:53

Anil · Answer 2 · 2020-08-27T08:56:35.617

2

This will work...

select * 
from (SELECT a, b, a+b as TOTAL FROM ( 
           select 7 as a, 8 as b FROM DUAL 
           UNION ALL 
           select 8 as a, 8 as b FROM DUAL 
           UNION ALL 
           select 0 as a, 0 as b FROM DUAL) 
    ) as Temp
WHERE TOTAL <> 0;

edited Aug 27 '20 at 08:56

answered Aug 27 '20 at 08:41

Anil

21
3

This answer is useful because of the additional `as Temp` which names the inner query. That is important for it to execute, at least in SQL Server. – Mike Finch Nov 25 '20 at 00:54

Use a calculated field in the where clause

2 Answers2