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年前70ビュー
回答なし

ログインしていません。 ログイン 回答を投稿する。

優れた回答とは、質問に明確に答え、建設的なフィードバックを提供し、質問者の専門分野におけるスキルの向上を促すものです。

質問に答えるためのガイドライン

関連するコンテンツ