Databricks Community

SQL · ‎11-15-2021

Hi Everyone,

I am using the below sql query to generate the days in order in hive & it is working fine. The table got migrated to delta and my query is failing. It would be appreciated if someone helps me to figure out the issue.

SQL Query :

with

explode_dates as (

with list_of_dates as (

SELECT CAST(date_column AS DATE) AS DAY

FROM (

VALUES (SEQUENCE(cast('2021-01-01' AS date), date_add('day',-1,current_date), INTERVAL '1' DAY) )

) AS t1(date_array)

CROSS JOIN UNNEST(date_array) AS t2(date_column)

)

select a.day as KPI_DATE, month(b.day) as dates_month, b.day as dates from list_of_dates a

join list_of_dates b on b.day between date_trunc('quarter', date_add('day',0,a.day)) and a.day )

jose_gonzalez · ‎11-15-2021

Hi @Arumugam Perumal ,

Please provide more details. What is the error you are getting? can you share the error stack trace?

SQL · ‎11-15-2021

Hi @Jose Gonzalez ,

I am getting below error message .I have changed the date_add function in order to compatible with Delta (date_add('day',-1,current_date), INTERVAL '1' DAY) ) --> date_add(current_date,-1))

Error Message:

ERROR processing query/statement. Error Code: 0, SQL state: org.apache.hive.service.cli.HiveSQLException: Error running query: org.apache.spark.sql.AnalysisException: cannot resolve '`date_array`' given input columns: [];