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.
Important: When you create an ETL function, it is available only to that script. It is not available to other scripts in the space.
Tip: 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 [SaveResults]()
WRITERECORD
END FUNCTION
[OrderID]=[Order Details.OrderID]
[ProductID]=[Order Details.ProductID]
[Quantity]=[Order Details.Quantity]
[New Quantity]=[MyFunction]([Order Details.Quantity],2.35)
[SaveResults]()