UDF in place of a Macro

Connectivity
Enthusiast

UDF in place of a Macro

Hi

I've the following macro, which calculates the business hours for a time period leaving out the weekends:

CREATE MACRO BHRS (CLOSEDTM TIMESTAMP, STRTDTM TIMESTAMP, BUSHRSPERDAY INTEGER, WKNDHRS INTEGER) AS
(
SELECT CAST (((:CLOSEDTM - :STRTDTM) DAY(4)) * :BUSHRSPERDAY AS INTEGER) + CAST (ABS (EXTRACT(HOUR FROM :STRTDTM) - EXTRACT(HOUR FROM :CLOSEDTM)) AS INTEGER) - CAST(COUNT(CALENDAR_DATE) * :WKNDHRS AS INTEGER)
FROM SYS_CALENDAR.CALENDAR WHERE DAY_OF_WEEK IN (1,7) AND CALENDAR_DATE BETWEEN CAST(:STRTDTM AS DATE) AND CAST(:CLOSEDTM AS DATE);
);

Just wondering, if I can replace the above macro as a UDF, so that this can be called at a row level. Any help & thoughts?

Thanks for your time.
4 REPLIES
Enthusiast

Re: UDF in place of a Macro

Are you asking for some help writing some C code?

Enthusiast

Re: UDF in place of a Macro

Not really. I was looking for some inputs on using an equivalent for sys_calendar.calendar table.
Enthusiast

Re: UDF in place of a Macro

Can't you just join your table with the calendar table on calendar_date = yourtable.datecol ?
Enthusiast

Re: UDF in place of a Macro

Yes. That's exactly I did, if you take a look at my macro. But, I was trying to ask if the same logic can be replicated as an UDF so that, Business Objects can use this at the row level.