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
gefragt vor 2 Jahren71 Aufrufe
Keine Antworten

Du bist nicht angemeldet. Anmelden um eine Antwort zu veröffentlichen.

Eine gute Antwort beantwortet die Frage klar, gibt konstruktives Feedback und fördert die berufliche Weiterentwicklung des Fragenstellers.

Richtlinien für die Beantwortung von Fragen