CODEWITHSUNDEEP
sqlsyntax-errorpresto
Pierre Halpern
Pierre Halpern

Reputation: 11

Unable to compare data with column in table created with WITH clause (Presto)

I am using Amazon Athena console, which is using presto. I have used the following WITH clause to get only the earliest entry for each customer who bought a bike in the "product_delivery" table:

WITH t1 AS 
    (SELECT customer_code, product_type, date_stamp
     FROM "products"."product_delivery" f1
     WHERE product_code like '%bike%'
     AND date_stamp = (SELECT MAX(date_stamp) 
         FROM "products"."product_delivery" f2 
         WHERE f1.product_code=f2.product_code)
    )

I then tried to find the same customers in the original delivery table who bought gloves at a date later than the date they got their first bike:

SELECT * 
FROM "products"."product_delivery" f3
WHERE customer_code IN (SELECT customer_code from t1)
AND product_code like '%gloves%'
AND (f3.date_stamp>t1.date_stamp WHERE f3.customer_code=t1.customer_code)

When I do that I get an error "column t1.date_stamp" cannot be resolved, even though I have created t1 with a date_stamp column just above.

How can I compare data from a table with the data in a table created with the WITH clause? Is there a different syntax for specifying a column when the table is made with a WITH clause?

Upvotes: 1

Views: 557

Answers (1)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

you dont have any join with t1 in f3 as a result you are not able to use date_stamp column of t1 sub-query in where clause, 1st join them and use like below 

    With t1 AS (
        SELECT customer_code, product_type, date_stamp
        FROM "products"."product_delivery" f1
        WHERE product_code like '%bike%'
        AND date_stamp = (SELECT MAX(date_stamp)
        from  "products"."product_delivery" f2
        WHERE f1.product_code=f2.product_code)
      ) ,
      t2 as    
     (
     SELECT * 
     FROM "products"."product_delivery" f3
     join t1 on f3.customer_code=t1.customer_code
     WHERE 
    customer_code IN (SELECT customer_code from t1) // as joined so this condition not required
    AND f3.product_code like '%gloves%'
    AND f3.date_stamp>t1.date_stamp
     )  select * from t2

Upvotes: 1

Related Questions