CODEWITHSUNDEEP
sqlhive
sds
sds

Reputation: 60014

Using new columns in the "where" clause

hive rejects this code:

select a, b, a+b as c
from t
where c > 0

saying Invalid table alias or column reference 'c'.

do I really need to write something like

select * from 
(select a, b, a+b as c
 from t)
where c > 0

EDIT:

  1. the computation of c it complex enough for me not to want to repeat it in where a + b > 0
  2. I need a solution which would work in hive

Upvotes: 4

Views: 8862

Answers (5)

GWines
GWines

Reputation: 65

You can run this query like this or with a Common Table Expression

select a, b, a+b as c
from t
where a+b > 0

Reference the below order of operations for logical query processing to know if you can use derived columns in another clause.

Keyed-In Order

  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY

Logical Querying Processing Phases

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

Upvotes: 3

spioter
spioter

Reputation: 1870

no

just:

select a, b, a+b as c from t where a+b > 0

note: for mysql at least: order by and group by you can use column (or expression) positions

e.g. group by 2, order by 1 would get you one row per column 2 (whether a field name or an expression) and order it by column 1 (field or expression)

also: some RDBMS's do let you refer to the column alias as you first attempted

Upvotes: 0

Sev09
Sev09

Reputation: 883

It would have to look like this:

select a, b, a+b as c
from t
where a+b > 0

An easy way to explain/remember this is this: SQL cannot reference aliases assigned within its own instance. It would, however, work if you did this:

SELECT a,b,c
FROM(
select a, b, a+b as c
from t) as [calc]
WHERE c > 0

This syntax would work because the alias is assigned in a subquery.

Upvotes: 1

Cam Bruce
Cam Bruce

Reputation: 5689

Use a Common Table Expression if you want to use derived columns.

with x as
(
select a, b, a+b as c
from t
)
select * from x where c >0

Upvotes: 5

user2989408
user2989408

Reputation: 3137

You are close, you can do this

select a, b, a+b as c
from t
where a+b > 0

Upvotes: 1

Related Questions