Using Custom DB Function
Calling custom DB functions is supported only for ORACLE, DB2, Oracle ODBC and MSSQL. User Defined Function (UDF) is a programming construct that accepts parameters, does actions and returns the response of that action.
- Define DB function The function name should not use campaign reserved
keywords/function names like below,
- ADD_MONTHS
- DATABASE_FUNCTION
- DB2_DAYS
- SUBSTR
- SUBSTRING
- LCLNUM
- DAYOF
- MONTHOF
- YEAROF
- CURRENT_DATE
- LTRIM
- RTRIM
- Define custom macro that uses custom DB function as defined by user
- Macro would be visible in formula editor.
- Use the macro.Note: Campaign will not do any syntax check. If arguments not passed as expected, will be runtime error.
Custom DB functions can be used in custom macro, derived field, expressions as part of select, extract, segement process box. For example, If you have defined custom DB function as MyCustomFunction, then to invoke the function user can write $MyCustomFunction (Arg1, Arg2, ... ).
Execution of these function are on database only. Hence, it is faster in execution as compared to other macros.
To create and use custom macro, refere Campaign User guide.
CustomMacroSchema
Configuration category
Campaign|partitions|partition[n]| dataSources|dataSourcename
Description
Specifies the schema used for calling user defined database functions.
The default value is blank.
For all data sources, set this property to the user of the database to which they are trying to call user defined database function.
Default value - No default value defined
Limitations:
Campaign supports UDF which returns a single value.
Campaign processes return value of custom db function as string upto 255 characters.