Custom Functions in ETL Scripts

You can define custom functions in ETL scripts to reuse code and improve script maintainability.

Note: An ETL function is available only within the script where it is defined. It cannot be accessed from other scripts in the same space.

Do not confuse ETL functions with BQL function called function(). These are separate and behave differently.

You must specify a return type when declaring an ETL function.

The return type must be Integer or Varchar.

This is an ETL function declaration:

Place the function invocation on the right side of an assignment statement.

FUNCTION [MyFunction]([Q] AS INTEGER,[R] AS FLOAT) AS INTEGER

Define the function name, a list of parameters and the output type. Define the function name, parameter list, and return type. Parameters and return types are optional, but if you include a return type, you must specify it explicitly.

FUNCTION [MyFunction]([Q] AS INTEGER,[R] AS FLOAT) AS INTEGER
   DIM [result] AS INTEGER = 0
   IF [Q]>10 THEN
      RETURN [Q]*[R]
   ELSEIF [Q]>=5 THEN
      [result]=0
      FOR [i] AS INTEGER = 1 TO 5
         [result]=[result]+[Q]
      NEXT
      RETURN [result]
   END IF
  RETURN 0
END FUNCTION

[OrderID]=[Order Details.OrderID]
[ProductID]=[Order Details.ProductID]
[Quantity]=[Order Details.Quantity]
[New Quantity]=[MyFunction]([Order Details.Quantity],2.35)
WRITERECORD