Recreate postgres query to snowflake query with Seq8() and generator functions-CodePudding

I am having a table in postgres, with below query :

 SELECT col_a, col_b, col_c, year1
    ,round(((year1::text || '-12-31')::date - d_start) / 30.5, 0) as mis
FROM (
    SELECT *, date_part('year', current_date)   generate_series(-2, 4) as year1
    FROM schema.table

I am migrating this query to snowflake as :

SELECT col_a, col_b, col_c, year1
    ,round(((year1::text || '-12-31')::date - d_start) / 30.5, 0) as mis
FROM (
 SELECT *, YEAR(CURRENT_DATE)   ROW_NUMBER() OVER(ORDER BY seq8())-3 
 FROM TABLE(GENERATOR(ROWCOUNT => 7)) as year1
    from schame.table

but in snowflake I am facing error, as it is not allowing from statement even tried below too, but with bad data result :

     SELECT col_a, col_b, col_c, year1
    ,round(((year1::text || '-12-31')::date - d_start) / 30.5, 0) as mis
FROM (
 SELECT *, YEAR(CURRENT_DATE)   ROW_NUMBER() OVER(ORDER BY seq8())-3 
 FROM TABLE(GENERATOR(ROWCOUNT => 7)) as year1
    ,
    from schame.table

The postgres query is giving the result generating year1 column values from :

2019 to 2025 repeatedly as

 |year1|
 |-----|
 |2019 |
 |2020 |
 |2021 |
 |2022 |
 |2023 |
 |2024 |
 |2025 |
 |2019 |
 |2020 |
 |2021 |
 |2022 |
 |2023 |
 |2024 |
 |2025 |

but in snowflake it is generating beyond 2025 even :

|year1|
 |-----|
 |2019 |
 |2020 |
 |2021 |
 |2022 |
 |2023 |
 |2024 |
 |2025 |
 |2026 |
 |2027 |
 |2028 |
 |2029 |
 |2030 |
 |2031 |
 |2032 |

What I am missing

Thanks for the help in advanvce

CodePudding user response：

For debugging purposes I tried the subquery in Snowflake:

 SELECT *, YEAR(CURRENT_DATE)   ROW_NUMBER() OVER(ORDER BY seq8())-3 
 FROM TABLE(GENERATOR(ROWCOUNT => 7)) as year1

The results are the years 2019 to 2025, as desired:

There's no bug in the code, unless there's more context you can give us.

CodePudding user response：

with cte (cy) as (select YEAR(CURRENT_DATE) (ROW_NUMBER() OVER(ORDER BY seq8()))-3 as year1 FROM TABLE(GENERATOR(ROWCOUNT => 7))) select b.*,year1 from cte, schema.table b

This is giving correct results.

The 3rd query in the question I have posted was doing cross join, that's why it was appending the year.