User Defined Functions (UDFs) or Macros

User-Defined Functions (UDFs) or macros in Data Augmentation Scripts enable you to create custom functions, which complement the built-in functions provided by Data Augmentation Scripts.

Syntax

macro_declaration ::= DEFINE FUNCTION macro_name '(' formal_parameter[,formal_parameter]... ')' 
                                                     value_returned_expression 
                                                 END

Example 1: The following UDF changes the date column format to yyyyMMdd:

DEFINE FUNCTION formatDate(col)
 DATE_FORMAT(col, 'yyyyMMdd')
END

Example 2: The following UDF demonstrates the usage of multiple input parameters:

DEFINE FUNCTION getCurrencyRate(currency, currencyRate)
 CASE WHEN currency = $VAR_PARAM_GLOBAL_CURRENCY$ THEN 1 ELSE currencyRate END
END

Using a UDF

To use UDFs in Data Augmentation Scripts, you must first define the function and then call the defined function.

You can define UDFs in two ways:

• Externally: You can define UDFs in the .func file and then call in the current locode application by including the .func file using the include_definition.
• Internally: You can define UDFs within the locode program itself

You can call UDFs in the following ways:

• UDF call on single column:

   macro_call ::= macro_name '(' { table_name.column_name | table-name [column_name] | THIS.column_name | THIS [column_name] } ')'

• UDF call on a list of columns:

  macro-call ::= macro_name '(' variable_name ')' FOR variable_name IN table-name [EXCLUDE] '[' column_name [, column_name]… ']'
  

Note:

• Data Augmentation Scripts supports a UDF call on a list of columns for single parameter UDF functions.
• The EXCLUDE option in UDF allows you to exclude specific elements of a dataset when applying the UDF.

Examples

• UDF defined externally

  You first define UDF in the myFunctions.func file within the Data Augmentation Scripts Project directory, and it's later used in the main.hrf file.

  myFunctions.func:

  DEFINE FUNCTION toVc(col)
     CAST(col AS VARCHAR2(400))
  END
  

  main.hrf:

  INCLUDE FUNCTION "myFunctions.func"
  
  DEFINE VERSIONED DATASET CUSTOMERS_D 
     ROWSOURCE CUSTOMERS WHERE CUSTOMERS.CUST_ID=1;
     THIS = CUSTOMERS;
     THIS[CUST_POSTAL_CODE_STRING] = toVc(CUSTOMERS.CUST_POSTAL_CODE);
     PRIMARYKEY[CUST_ID];
  END
  

• UDF defined internally
  You can define UDFs within the Data Augmentation Scripts program in the .hrf itself.

  DEFINE FUNCTION toVc(col)
     CAST(col AS VARCHAR2(400))
  END
  
  DEFINE VERSIONED DATASET CUSTOMERS_D 
     ROWSOURCE CUSTOMERS WHERE CUSTOMERS.CUST_ID=1;
     THIS = CUSTOMERS;
     THIS[CUST_POSTAL_CODE_STRING] = toVc(CUSTOMERS.CUST_POSTAL_CODE);
     PRIMARYKEY[CUST_ID];
  END