Interval values with month or year parts are not supported

0

Hello Redshift team,

I am trying to understand as to why the query fails when a variable is introduced to it.

The below query expands date range, and returns the list. In the "date_list" CTE table expression, a variable 'n' is referenced, which causes the query to fail with the error "Interval values with month or year parts are not supported"

Query 1:

WITH recursive numbers(n) as
( 
  SELECT 0 as n
    UNION all
    SELECT n + 1
    FROM numbers n
    WHERE n.n <= datediff(year,'1980-01-01'::date,trunc(current_date)) 
),
date_list as (
    SELECT n as id,trunc('1980-01-01'::date + n * interval '1 year') as date_v FROM numbers
  )
 select * from date_list

However the following query same as above one succeeds when the variable 'n' is substituted with a static value of 10 in the "date_list" CTE table expression.

WITH recursive numbers(n) as
( 
  SELECT 0 as n
    UNION all
    SELECT n + 1
    FROM numbers n
    WHERE n.n <= datediff(year,'1980-01-01'::date,trunc(current_date)) 
),
date_list as (
    SELECT n as id,trunc('1980-01-01'::date + 10 * interval '1 year') as date_v FROM numbers
  )
 select * from date_list

Can you please help me understand, why year interval executes successfully, when passing a static value over a dynamic variable?

Cluster details:
Version: 1.0.36224
Region: us-east-1
SQL Client: Redshift Query Editor V2

AJP
已提問 2 年前檢視次數 71 次
沒有答案

您尚未登入。 登入 去張貼答案。

一個好的回答可以清楚地回答問題並提供建設性的意見回饋,同時有助於提問者的專業成長。

回答問題指南