Skip to Main Content

Alias a function

PugzlyNov 25 2022

I have a generic function that generates intervals (seconds, minutes, hours or days), which works fine.

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

When I try to alias it to exclude weekends and holidays I get a syntax error.

ORA-00942: table or view does not exist

I can't seem to pinpoint the problem and was hoping someone can help me.

create table holidays(
holiday_date DATE not null,
holiday_name VARCHAR2(20),
constraint holidays_pk primary key (holiday_date),
constraint is_midnight check ( holiday_date = trunc ( holiday_date ) )
);

INSERT into holidays (HOLIDAY_DATE,HOLIDAY_NAME)
WITH dts as (
select to_date('15-APR-2022 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'Passover 2022' from dual union all
select to_date('31-DEC-2022 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'New Year Eve 2022' from dual
)
SELECT * from dts;

SELECT
c.dt
FROM generate_dates(
TIMESTAMP '2022-11-03 00:00:00',
TIMESTAMP '2022-11-18 00:00:00',
1, 'DAY') c
where
to_char(c.dt, 'DY') NOT IN ('SAT', 'SUN')
AND NOT EXISTS (
SELECT 1
FROM holidays h
WHERE c.dt = h.holiday_date
);

This post has been answered by Frank Kulash on Nov 25 2022
Jump to Answer
Comments
Post Details
Added on Nov 25 2022
6 comments
64 views