CODEWITHSUNDEEP
sqloracle-databaseplsqloracle12c
Shree Naikawadi
Shree Naikawadi

Reputation: 13

Is there any alternative of generate_series()function in oracle

I am migrating structure of PostgreSQL to Oracle 12c but somewhere i am finding difficulties converting Generate_series function to oracle. is there any solution for this .

The code provided below i have used in postgresql.

SELECT (my_day - elapsed_my_day)
     INTO return_v
     FROM (select count(1) elapsed_my_day from
     generate_series(trunc(fromdate), trunc(todate) - 1, '1 day' interval) i;

What would be the proper syntax of generate_series() function in Oracle.

Upvotes: 0

Views: 1178

Answers (1)

MT0
MT0

Reputation: 168361

You appear to be counting the number of days between two dates and then subtracting that offset from another day. You don't need to generate a series and count it; just subtract from dates. You don't even need to use a SQL query, it can be purely written in PL/SQL:

BEGIN
  return_v := m_day - ( TRUNC( to_date ) - 1 - TRUNC( from_date ) );
END;

Upvotes: 2

Related Questions