Simple demo for creation of Custom Functions

[Former Member]

We can use Data Services scripting language to create

1)Single use components call scripts

2)Reusable components called Custom functions

This demo involves creation of both a script and a Custom Function.

We can create reusable functions in Data Services scripting language using the smart editor. To create a custom function, right click on the “Custom Function” tab, and click on “New”.

Provide the function name and a brief description of its functionalities.

By default the return type of the function is int. In the function GET_DEPTNAME we want the function to return a string containing department name.  So I needed to change the return type to varchar. To do this, I right click on the “Return  “ and choose “Properties”.

I change the Return value properties to varchar 30.

Next I declare a local variable for my function by right-clicking “Local” and selecting “Insert…”

I declare a local variable  named $LOCAL_VARIABLE of type varchar 30.

I right click on Parameter , to insert a input parameter named $DEPARTMENT_ID of type varchar 4.

I write a function that reads the department name from the database, based on the input department Id,  and  stores it in the local variable. The content of the local variable is returned by the function.

$LOCAL_VARIABLE= SQL('MY_DATASTORE’,'SELECT DEPTNAME FROM DEPARTMENT WHERE DEPT_ID={$DEPARTMENT_ID}');

RETURN $LOCAL_VARIABLE;

The table that I am referring to has 3 columns as shown in below figure.

I call the function from a script, in a job JOB_DEMO_4.

The script has a single line call to the function that I have just created. When I run the job, the department name for department Id='M10' is printed.