Siebel Analytics Server Administration Guide > SQL Reference > SQL Reference >

Conversion Functions


The conversion functions convert a value from one form to another.

Cast

Changes the data type of either a numeric value or a null value to another data type.

Syntax:

CAST (expression|NULL AS datatype)

For example, you can cast a customer_name (a data type of Char or Varchar) or birthdate (a datetime literal). The supported data types to which the value can be changed are the following:

CHARACTER, VARCHAR, INTEGER, FLOAT, SMALLINT, DOUBLE PRECISION, DATE, TIME, TIMESTAMP, BIT, BIT VARYING

Depending on the source data type, some destination types are not supported. For example, if the source data type is a BIT string, the destination data type has to be a character string or another BIT string.

IfNull

Tests if an expression evaluates to a null value, and if it does, assigns the specified value to the expression.

Syntax:

IFNULL (expression, value)

VALUEOF( )

Use the VALUEOF function in an expression builder or filter to reference the value of a repository variable defined in the Administration Tool. You can also use the VALUEOF function when you edit the SQL for a request from the Advanced tab in Siebel Answers.

Variables should be used as arguments of the VALUEOF function. Refer to static repository variables by name. For example, to use the value of a static repository variables named "prime_begin" and "prime_end":

CASE WHEN "Hour" >= VALUEOF("prime_begin")AND "Hour" < VALUEOF("prime_end") THEN 'Prime Time' WHEN ... ELSE...END

You need to refer to a dynamic repository variable by its fully qualified name. If you are using a dynamic repository variable, the names of the initialization block and the repository variable need to be enclosed in double quotes ( " ), separated by a period, and contained within parentheses. For example, to use the value of a dynamic repository variable named REGION contained in an initialization block named Region Security, this is an example of the proper syntax to use:

SalesSubjectArea.Customer.Region =

VALUEOF("Region Security"."REGION")

The names of session variables need to be preceded by NQ_SESSION, separated by a period, and contained within parentheses. If the variable name contains a space, enclose the name in double quotes ( " ). For example, to use the value of a session variable named REGION, this is an example of the proper syntax to use in an expression builder (filter):

"SalesSubjectArea"."Customer"."Region" = VALUEOF(NQ_SESSION.REGION)

NOTE:  Although using initialization block names with session variables (just as with other repository variables) may work, you should use NQ_SESSION. NQ_SESSION acts like a wild card that matches all initialization block names. This allows the administrator to change the structure of the initialization blocks in a localized manner without impacting reports.

Siebel Analytics Server Administration Guide