Column 'month' cannot be resolved

Question

I am working on a view in Athena, which requires being able to do the following, identify the time of each work experience, using the number of months, days and count how many jobs you have had in your work experience and the month field is not being recognized in the code, for that the following was done:

CREATE OR REPLACE VIEW "bi_applied_offers" AS 
SELECT DISTINCT
  applied_offers.id application_id,
  applied_offers.applied_date applied_date,
  year(applied_offers.applied_date),
  month(applied_offers.applied_date),
  ... [rest of your SELECT fields here] ...
ARRAY_JOIN(ARRAY_AGG(DISTINCT concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(work_experiences.company_name, ' '), COALESCE(CAST(work_experiences.started_at AS varchar), '-')), ' a '), COALESCE(CAST(work_experiences.finished_at AS varchar), 'En progreso')), ' durante '), COALESCE(CAST(work_experiences.time_period AS varchar), '')), ' meses como '), work_positions_work_experiences.description), ' --- '), job_categories_work_experiences.description)), ', ') work_experiences_description,
  COUNT(DISTINCT work_experiences.id) AS num_of_experiences,
  SUM(
      CASE 
          WHEN work_experiences.finished_at IS NOT NULL THEN 
              DATEDIFF(MONTH, work_experiences.started_at, work_experiences.finished_at)
          ELSE 
              DATEDIFF(MONTH, work_experiences.started_at, CURRENT_DATE)
      END
  ) AS total_experience_duration_months
FROM
... [your JOINs and other clauses here] ...

WHERE (applied_offers.created_at > CAST('2019-01-01' AS DATE))
GROUP BY applied_offers.id, applied_offers.applied_date, year(applied_offers.applied_date), month(applied_offers.applied_date), ... [rest of your GROUP BY fields here] ...

Answer

Hi,

It could be that `month` is a reserved word, and you need to either escape it or use:

```
CREATE OR REPLACE VIEW "bi_applied_offers" AS SELECT DISTINCT applied_offers.id application_id, 
applied_offers.applied_date applied_date, year(applied_offers.applied_date) AS application_year, 
month(applied_offers.applied_date) AS application_month...
```
and use `application_month` as a reference to this column later.

Column 'month' cannot be resolved

Relevant content