0

I have a segment of code written in Hiveql that generates 3 columns, a index, a date, and a range of dates:

drop table if exists date_list;
create temporary table date_list as
with seq as(
select date_add('2020-02-27',s.i) as dt
from(
select posexplode(split(space(datediff('2020-12-01','2020-02-27')),' ')) as (i,x)
)s
)
select *,
row_number() over() index_n,
int(REGEXP_REPLACE(substring(dt,6,10),'\\-','')) as date_index

from seq;

Output:

enter image description here

. . .

enter image description here

Now, I need to convert to Redshift syntax due to a database migration, functions such as space, split do not exist in Redshift. How do I convert the code to be able to run in Redshift?

Running the same code generates such error:

[Code: 500310, SQL State: 42601]  [Amazon](500310) Invalid operation: syntax error at or near ")" 
Position: 169;

Thanks!

lydias
  • 841
  • 1
  • 14
  • 32

1 Answers1

1

This is done with a recursive cte. I answered a similar question a bit ago.

trying to create a date table in Redshift

Generating the other columns from the list of dates is straight forward.

Bill Weiner
  • 8,835
  • 2
  • 7
  • 18