CODEWITHSUNDEEP
sqlgoogle-bigquery
d-_-b
d-_-b

Reputation: 23171

BigQuery alias case and use in where clause

Can someone help me understand what's wrong with this query?

BigQuery is showing an error:

Error: Unrecognized name: case1 at [...]

SELECT
  col1,
  CASE
    WHEN col2 IN ('text1') THEN 1
    ELSE 0
  END as case1
  CASE
    WHEN col2 IN ('text2') THEN 1
    ELSE 0
  END as case2
FROM `dataset.tablename`
WHERE case1 = 1 OR case2 = 1

Upvotes: 1

Views: 5809

Answers (1)

Andrew
Andrew

Reputation: 760

SELECT
  col1,
  CASE
    WHEN col2 IN ('text') THEN 1
    ELSE 0
  END as case1
FROM `dataset.tablename`
WHERE CASE
    WHEN col2 IN ('text') THEN 1
    ELSE 0
  END = 1

You can't query on an alias in the same statement

Upvotes: 3

Related Questions