CODEWITHSUNDEEP
sqloracle-databasepostgresqldatetimerecursive-query
jon
jon

Reputation: 73

Convert oracle Sql to postgresql with "CONNECT BY level"

as "CONNECT BY level" keywords is not supported on PostgreSQL i couldn't translate below sql to PostgreSQL

Please advise how can I rewrite below oracle SQL on PostgreSQL :

SELECT
    TO_DATE('01/01/2019','MM/DD/YYYY') + level - 1 AS days,
    level AS level1
FROM
    dual
CONNECT BY
    level <= (
        SELECT
            abs(TO_DATE('01/01/2021','MM/DD/YYYY') - TO_DATE('01/10/2021','MM/DD/YYYY') )
        FROM
            dual
        WHERE
            'Daily' = 'Daily'
    )

result :

01-JAN-19   1
02-JAN-19   2
03-JAN-19   3
04-JAN-19   4
05-JAN-19   5
06-JAN-19   6
07-JAN-19   7
08-JAN-19   8
09-JAN-19   9

Thanks

Upvotes: 1

Views: 2565

Answers (1)

GMB
GMB

Reputation: 222582

Postgres has a handy function called generate_series(), which makes this kind of task easy:

select dt::date, rn
from generate_series('2019-01-01'::date, '2019-01-09'::date, '1 day') 
    with ordinality as d(dt, rn)

I don't see the point for the obsfucated logic in the connect by clause of the Oracle query; the scalar subquery always evaluates as 9.

Demo on DB Fiddlde:

dt         | rn
:--------- | -:
2019-01-01 |  1
2019-01-02 |  2
2019-01-03 |  3
2019-01-04 |  4
2019-01-05 |  5
2019-01-06 |  6
2019-01-07 |  7
2019-01-08 |  8
2019-01-09 |  9

Upvotes: 2

Related Questions