Custom Functions in ETL Scripts

You can define new functions in ETL scripts. This enables code re-use as well as tighter and more maintainable scripts.

Note: When creating an ETL function, it is available only to that script. It is not available to other scripts in the space.

There is also a BQL function called function() that is different from an ETL function.

An ETL function declaration is as follows:

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

Next you define the function name, a list of parameters and the output type. Parameters need not be defined and likewise a return type need not be defined:

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

FUNCTION
   WRITERECORD
END FUNCTION

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