I come from SQL Server and some times I'm not familiar to Oracle syntax, I want to create a function that takes a date and number of dates as a parameters and create a table function.

My original query is:

VAR TREND = 1;
VAR OBS_DATE = 20221109;
VAR N_DAYS = 21;

WITH CAL AS
(
    SELECT
        TO_DATE(:OBS_DATE, 'YYYYMMDD')   (LEVEL - 1 * :TREND) DT, ROW_NUMBER() OVER(ORDER BY NULL) - 1 IX
    FROM 
        DUAL
    WHERE 
        TO_CHAR(TO_DATE(:OBS_DATE, 'YYYYMMDD')   (LEVEL - 1 * :TREND) , 'D') NOT IN (1,7)
    CONNECT BY LEVEL <= :N_DAYS   :N_DAYS/5*2 1
)
SELECT DT 
FROM CAL 
WHERE IX <= :N_DAYS;

But when I try to convert as a function it sends me an error and I don't know what the correct syntax is.

My attempt is:

CREATE OR REPLACE FUNCTION FUN_BUS_CALENDAR(
    OBS_DATE IN DATE := SYSDATE
    , NDAYS IN NUMBER
    , TREND IN NUMBER
    )
    RETURN OBS_DATE DATE;
    BEGIN

    WITH CAL AS(
        SELECT
            TO_DATE(:OBS_DATE, 'YYYYMMDD')   (LEVEL - 1 * :TREND) OBS_DATE, ROW_NUMBER() OVER(ORDER BY NULL) - 1 IX
        FROM DUAL
        WHERE TO_CHAR(TO_DATE(:OBS_DATE, 'YYYYMMDD')   (LEVEL - 1 * :TREND) , 'D') NOT IN (1,7)
        CONNECT BY LEVEL <= :N_DAYS   :N_DAYS/5.*2. 1.
    )
    SELECT OBS_DATE FROM CAL WHERE IX <= :N_DAYS
        RETURN OBS_DATE
    END 
/

CodePudding user response：

You should probably just use the initial query.

However, if you did want a function then you can use a pipelined function:

CREATE FUNCTION BARRRAF.FUN_BUS_CALENDAR(
    OBS_DATE IN DATE := SYSDATE,
    NDAYS    IN NUMBER,
    TREND IN NUMBER
) RETURN SYS.ODCIDATELIST PIPELINED
IS
BEGIN
  FOR i IN 1 .. ndays LOOP
    PIPE ROW( obs_date   i - trend );
  END LOOP;
END;
/

Then if you want to generate a row number then just use a sub-query:

SELECT column_value AS obs_date,
       ROWNUM - 1 AS rn
FROM   TABLE(BARRRAF.FUN_BUS_CALENDAR(ndays => 3, trend=>1))

Which outputs:

fiddle

CodePudding user response：

Here is a generic function that can be used to create a calendar for the following INTERVALs seconds, minutes, hours or days.

You can pass it any start and END date/time you like. The lower or higher date can go in any position as there is logic to figure out which is what least/greatest command

CREATE OR REPLACE FUNCTION generate_dates(i_from_dat IN TIMESTAMP, i_to_dat IN TIMESTAMP, i_interval IN NUMBER, i_interval_type IN VARCHAR2)
RETURN VARCHAR2
SQL_MACRO
IS
BEGIN
    RETURN q'~SELECT LEAST(i_from_dat,i_to_dat)   NUMTODSINTERVAL( (LEVEL-1)*i_interval, i_interval_type ) AS dt
    FROM DUAL
    CONNECT BY LEAST(i_from_dat,i_to_dat)    NUMTODSINTERVAL( (LEVEL-1)*i_interval, i_interval_type) < GREATEST(i_from_dat, i_to_dat)~';
END ;

SELECT * FROM generate_dates(
TIMESTAMP '2022-11-03 09:47:31',
TIMESTAMP '2022-11-03 12:37:11',
 30, 'MINUTE') ;

DT
03-NOV-22 09.47.31.000000 AM
03-NOV-22 10.17.31.000000 AM
03-NOV-22 10.47.31.000000 AM
03-NOV-22 11.17.31.000000 AM
03-NOV-22 11.47.31.000000 AM
03-NOV-22 12.17.31.000000 PM

SELECT * FROM generate_dates(
TIMESTAMP '2022-11-03 00:00:00',
TIMESTAMP '2022-11-08 00:00:00',
 1, 'DAY') ;

DT
03-NOV-22 12.00.00.000000 AM
04-NOV-22 12.00.00.000000 AM
05-NOV-22 12.00.00.000000 AM
06-NOV-22 12.00.00.000000 AM
07-NOV-22 12.00.00.000000 AM