CODEWITHSUNDEEP
sqlgoogle-bigquerycross-join
Thao Ly
Thao Ly

Reputation: 33

How to use column alias in where clause

enter image description here

I'm trying to remove all lines that have productRef = productAssociated.
I tried the query below, but the last line doesn't work.

What's going wrong?

SELECT date, transaction.transactionId, 
       ref.productSKU as productRef, 
       associated.productSKU as productAssociated, 
       ARRAY_LENGTH(hits.product) as nbProducts
FROM `dl-recommendation-engine.NDA_CHANEL_137002018.ga_sessions_*` as session,
     UNNEST(hits) AS hits,
     UNNEST(hits.product) as ref,
     UNNEST(hits.product) as associated
WHERE _TABLE_SUFFIX BETWEEN '20191122' AND '20191202' AND
      hits.transaction.transactionId IS NOT NULL AND
     ARRAY_LENGTH(hits.product) > 2 AND
     productAssociated != productRef

Upvotes: 0

Views: 260

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You cannot use table aliases in the where clause.

Instead, just use the expressions:

WHERE _TABLE_SUFFIX BETWEEN '20191122' AND '20191202' AND
      hits.transaction.transactionId IS NOT NULL AND
      ARRAY_LENGTH(hits.product) > 2 AND
      associated.productSKU <> ref.productSKU

Upvotes: 1

Related Questions