Migration of Oracle UDF to Teradata

Database
Enthusiast

Migration of Oracle UDF to Teradata

Hi,

I am trying to migrate an Oracle view to Teradata

which uses oracle user defined function in select clause

and function contains the logic which cannot be migrated to TD SQL UDF.

Has anyone faced this situation before?

Is there any other approach apart from writing c/java UDF?

If yes kindly share what aproach was used to get around this situation?

The oracle udf source code is described below,

CREATE OR REPLACE FUNCTION BT (

P_START_DTM              IN DATE,

P_END_DTM                IN DATE,

P_APPLY_BUSINESS_HOURS      NUMBER)

RETURN NUMBER

IS

START_DTM               DATE;

END_DTM                 DATE;

START_HOUR              NUMBER;

END_HOUR                NUMBER;

START_MINS              NUMBER;

END_MINS                NUMBER;

DEBUG_MODE              BOOLEAN;

BUSINESS_OPEN           DATE;

BUSINESS_CLOSE          DATE;

BUSINESS_TIME           DATE;

WORKING_DAYS            NUMBER;

WORKING_MINUTES         NUMBER;

INCREMENT               NUMBER;

NON_BIZ_END             NUMBER;

NON_BIZ_START           NUMBER;

WORKING_DAYS_EXCL_HOL   NUMBER;

BEGIN

-- DEFINE CONSTANTS

DEBUG_MODE = FALSE;

BUSINESS_OPEN = TO_DATE ('08:00', 'HH24:Mi');

BUSINESS_CLOSE = TO_DATE ('17:00', 'HH24:Mi');

INCREMENT = 1;                                              --default is 1

-- INITIALISE VARIABLES

START_DTM = P_START_DTM;

END_DTM = P_END_DTM;

BUSINESS_TIME = TO_DATE ('00:00', 'HH24:Mi');

IF (START_DTM > END_DTM)

THEN

RETURN 0;

END IF;

IF (TO_DATE (TO_CHAR (START_DTM, 'HH24:Mi'), 'HH24:Mi') < BUSINESS_OPEN)

THEN

START_DTM =

START_DTM

+ (  BUSINESS_OPEN

- TO_DATE (TO_CHAR (START_DTM, 'HH24:Mi'), 'HH24:Mi'));

START_HOUR = TO_NUMBER (TO_CHAR (START_DTM, 'HH24'));

START_MINS = TO_NUMBER (TO_CHAR (START_DTM, 'Mi'));

END IF;

IF (TO_DATE (TO_CHAR (END_DTM, 'HH24:Mi'), 'HH24:Mi') < BUSINESS_OPEN)

THEN

IF TRUNC (END_DTM) = TRUNC (START_DTM)

THEN

END_DTM =

END_DTM

+ (  BUSINESS_OPEN

- TO_DATE (TO_CHAR (END_DTM, 'HH24:Mi'), 'HH24:Mi'));

ELSE

END_DTM = TRUNC (END_DTM) - 1;

END_DTM =

END_DTM

+ (  BUSINESS_CLOSE

- TO_DATE (TO_CHAR (END_DTM, 'HH24:Mi'), 'HH24:Mi'));

END IF;

END_HOUR = TO_NUMBER (TO_CHAR (END_DTM, 'HH24'));

END_MINS = TO_NUMBER (TO_CHAR (END_DTM, 'Mi'));

END IF;

IF (TO_DATE (TO_CHAR (START_DTM, 'HH24:Mi'), 'HH24:Mi') > BUSINESS_CLOSE)

THEN

START_DTM =

START_DTM

+ (  BUSINESS_CLOSE

- TO_DATE (TO_CHAR (START_DTM, 'HH24:Mi'), 'HH24:Mi'));

START_HOUR = TO_NUMBER (TO_CHAR (START_DTM, 'HH24'));

START_MINS = TO_NUMBER (TO_CHAR (START_DTM, 'Mi'));

END IF;

IF (TO_DATE (TO_CHAR (END_DTM, 'HH24:Mi'), 'HH24:Mi') > BUSINESS_CLOSE)

THEN

END_DTM =

END_DTM

+ (  BUSINESS_CLOSE

- TO_DATE (TO_CHAR (END_DTM, 'HH24:Mi'), 'HH24:Mi'));

END_HOUR = TO_NUMBER (TO_CHAR (END_DTM, 'HH24'));

END_MINS = TO_NUMBER (TO_CHAR (END_DTM, 'Mi'));

END IF;

SELECT COUNT (*)

as WORKING_DAYS

FROM DIM_D D

WHERE     FULL_DATE BETWEEN TRUNC (START_DTM) AND TRUNC (END_DTM)

AND D.PNC_STD_HOLIDAY_FLG IS NULL

AND D.TYPE_OF_DAY = 'Weekday';

WORKING_MINUTES = 0;

IF (TO_CHAR (END_DTM, 'DY') NOT IN ('SAT', 'SUN'))

THEN

WORKING_MINUTES =

(  TO_DATE (TO_CHAR (END_DTM, 'HH24:Mi'), 'HH24:Mi')

- TO_DATE (TO_CHAR (BUSINESS_CLOSE, 'HH24:Mi'), 'HH24:Mi'))

* 24

* 60;

END IF;

IF (TO_CHAR (START_DTM, 'DY') NOT IN ('SAT', 'SUN'))

THEN

WORKING_MINUTES =

WORKING_MINUTES

+ (  (  TO_DATE (TO_CHAR (BUSINESS_OPEN, 'HH24:Mi'), 'HH24:Mi')

- TO_DATE (TO_CHAR (START_DTM, 'HH24:Mi'), 'HH24:Mi'))

* 24

* 60);

END IF;

IF (WORKING_DAYS <= 0)

THEN

WORKING_MINUTES = 0;

BUSINESS_TIME = BUSINESS_TIME + (WORKING_MINUTES / 60 / 24);

END IF;

IF (WORKING_DAYS > 0)

THEN

BUSINESS_TIME =

BUSINESS_TIME

+ ( ( ( (BUSINESS_CLOSE - BUSINESS_OPEN) * 24) * WORKING_DAYS) / 24)

+ (WORKING_MINUTES / 60 / 24);

END IF;

RETURN ROUND ( (BUSINESS_TIME - TO_DATE ('00:00', 'HH24:Mi')) * 24 * 60);

END;

2 REPLIES
Junior Supporter

Re: Migration of Oracle UDF to Teradata

Hi Vinaywani,

While migrating Oracle to TD we converted UDF's to store procs in Teradata. There may not be same functions available but ya the same logic as far I could see in your function could be created. Please let me know the correct background of this function. Are you planning to pass the values one by one same as Oracle or are you planning to convert the same to a SET based logic. I would prefer going for SET based approach.

Teradata Employee

Re: Migration of Oracle UDF to Teradata

Vinaywani,

I would guess that the output of this function is a parameter that is used in an SQL query.  In that case, it would not be practical to do this as a stored procedure unless that query were also always run from a stored procedure.

If this value is not used in a stored procedure but rather is used in a user's SQL, then you could transform the logic of this function into a view which would produce the same value and would be product-joined to the base SQL.  A cursory look at the code suggests to me that all your IF statements could be written as nested CASE operations in Set SQL.  For ideas on how to do this, see my blog at http://developer.teradata.com/blog/georgecoleman.

George