To create a User-Defined Function (UDF), you must write your own code, dynamically link that code into the server, and then call that code from within a CCL statement.
For example, if you wanted to calculate the monthly mortgage payment on a loan, you could write a UDF named "MonthlyMortgagePayment()" and call it as shown below:
INSERT INTO ... SELECT MonthlyMortgagePayment(loanAmt, interestRate, NumOfMonths) ...
When the user-defined function is called from a CCL query, the Sybase CEP Server will call the function with the appropriate data. In the example above, the user's function will be passed the values that are in the loanAmt, interestRate, and numOfMonths columns of the current row. The parameter(s) passed to the function must be in the form of a proper CCL expression, which may be a column name, a literal expression, or a more complex expression. For example:
INSERT INTO OutputStream SELECT * FROM InputStream WHERE f1(InputStream.MyIntCol, InputStream.col2 / 100, 'A Literal', f2(f3(x))) > 1000;
A UDF can appear in most parts of a CCL statement where a general expression can appear.
A UDF can appear more than once in the same statement. For example:
INSERT INTO stream_Out SELECT MY_UDF(my_col) AS f_Current, MY_UDF(my_col) - PREV(my_col) AS f_Delta FROM stream_In;
UDFs may be scalar functions or aggregate functions. Scalar functions return a value based on a single set of inputs (for example, one or more values from a single row, possibly with other expressions that are independent of any row). The built-in SQRT() function is a typical scalar function. Aggregate functions return a value based on 0 or more rows. The built-in SUM() and AVG() functions are typical aggregate functions.
For each row, if the same UDF is called with the same parameter value(s), the server may either execute the function once for each time the function is called from within the statement, or the server may call the function fewer times and cache the results. The server may then use the cached results some of the times when the function would have been called.
For aggregate functions, the server will call the function once for each time that the function exists in the statement.
For non-aggregate functions, the number of times that the function is called (with the same parameter value) is determined internally by the server and is not predictable or controllable by the user.
If you are writing your own UDF, in almost all cases the function should be idempotent, in other words, it should return the same value regardless of how many times it is called within the same statement. Only in very rare cases, such as the built-in NEXTVAL() function, should a function deliberately return a different value for each call with the same value inside the same statement.
A UDF can contain almost any code that you can write in any of the languages for which Sybase CEP provides an SDK that supports UDFs.
UDFs allow you to provide for your own specialized needs. The range of capability that you may add is almost unlimited. The UDF may even go "outside" the environment of the Sybase CEP Server in which it is running. For example, you might write a function that would query a high-precision pi() value from a university mainframe using SOAP (Simple Object Access Protocol).