CODEWITHSUNDEEP
sqloracle-databaseoracle8i
Bitterblue
Bitterblue

Reputation: 14075

Use field aliases in the same query?

How ... or can I use a field alias a.k.a. field as val in the very same select-clause and the where-clause ?

select
    a.LONG_NAME_I_CANT_REMEMBER as val1,
    a.NAME_I_DONT_WANT_2_REMEMBER as val2,
    val1-val2 as diff
from
    random_table a
where
    (val1 > 12)

There a re no inline-view / inline queries and it's not about table aliases.

Upvotes: 0

Views: 76

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

You can't use an alias you define in the SELECT clause in your WHERE clause. Logically, determining which rows to return (applying the WHERE clause) comes before determining which columns to display (the SELECT list) so when the WHERE clause is processed, the aliases don't exist yet.

You could, of course, refactor the query so that you're selecting and aliasing the data in an inline view and then apply the WHERE clause outside that view. Something like

select a.*,
       val1-val2 as diff
  from (select a.LONG_NAME_I_CANT_REMEMBER as val1,
               a.NAME_I_DONT_WANT_2_REMEMBER as val2,
          from random_table a) a
 where (val1 > 12)

Upvotes: 3

Related Questions