Writing scalar functions

Scalar functions accept zero or more arguments and return a single value, which may be a null value. They are invoked in the same way as any scalar function in a dynamic query or a database query. They can be included as part of an expression in a model, a report specification. or a Cognos SQL statement.

Scalar functions can be written in SQL or the Java™ programming language. Java scalar functions are implemented as static methods of a class. The input parameters and returned value can be any Java type that maps to a supported dynamic query extensibility data type. See Data type conversions from JDBC/SQL data types to Java data types for a list of supported data type mappings.

SQL scalar function example

SQL scalar functions are contained in a deployment descriptor file. This is an example of an SQL scalar function that convert temperatures from Celsius to Fahrenheit.

CREATE FUNCTION CELSIUS_TO_FAHRENHEIT(C INTEGER)
RETURNS FLOAT
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
RETURN (C * 9) / 5 + 32;

This function can be used in a SQL statement like any other scalar function. For example,

SELECT CELSIUS, CELSIUS_TO_FAHRENHEIT(CELSIUS) FAHRENHEIT
FROM TEMPERATURES

This query produces the following result.

Table 1. SQL scalar function result
CELSIUS	FAHRENHEIT
0	32.0
100	212.0

A screenshot using this sample function is shown here.

Image showing celsius_to_fahrenheit expression — Figure 1. Sample function use in Cognos Reporting

Java scalar function example

This Java example formats currency based on country codes. The Java code is included in the Format.java sample program.

The following snippet in a deployment descriptor file is associated with this example.

CREATE FUNCTION formatCurrency(V DECIMAL(10,2), LANG VARCHAR(32), COUNTRY VARCHAR(32))
RETURNS VARCHAR(32)
LANGUAGE JAVA
PARAMETER STYLE JAVA
EXTERNAL NAME 'thisjar:udf.samples.Format.formatCurrency';

The external name is the fully qualified method name (package.class.method) that contains the implementation of the logic for the function

This function can be used in a SQL statement like any other scalar function. For example,

SELECT PNAME, PRICE, formatCurrency(PRICE, 'en', 'US') FORMATTED_PRICE
FROM PRODUCTS

This query produces the following result.

Table 2. SQL scalar function result
PNAME	PRICE	FORMATTED_PRICE
Bolt	1.40	$1.40
Screw	1.50	$1.50