How do I fix a select statement in the join in Athena?

This query was working in Redshift but isn’t in Amazon Athena:

    SELECT DISTINCT t1.place_id, t1.date, flg
    FROM rec t1
    LEFT JOIN (
      SELECT date, place_id,
      CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS flg
      FROM rec
      GROUP BY date, place_id
    ) t2 ON t1.one_year_ago_dow_day = t2.date AND t1.place_id = t2.place_id

The error message is:

------------------------------------------
Your query has the following error(s):

SYNTAX_ERROR: line 84:39: Column 't2.date' cannot be resolved

This query ran against the "~~~" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: 29311d73-2cdb-4279-b88c-xxxxxxx
--------------------------------------

The error says “t2.date” is the problem, is it because I am selecting the table in left join?

How do I get this SQL to work in Athena?

Any insight would be appreciated.

Answer

If the date field is causing problems (as suggested by @Deepstop), you can try:

    SELECT DISTINCT t1.place_id, t1.date, flg
    FROM rec t1
    LEFT JOIN (
      SELECT
        date as date1,  -- Changed here, and in the ON below
        place_id,
        CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS flg
      FROM rec
      GROUP BY date, place_id
    ) t2 ON (t1.one_year_ago_dow_day = t2.date1) AND (t1.place_id = t2.place_id)

Advertisement

Answer