These functions are valid only for use only in SQL expression of a record source and can not be used in a field, computed field, parameter expression or within label/report/request code.

 

Function

Description

IfNull (exp,value)

if exp is null, value is returned else exp

CaseWhen (exp,v1,v2)

if exp is true, v1 is returned, else v2

Convert (term,type)

converts exp to another data type

Cast (term AS type)

converts exp to another data type

 

Note that the data types of table fields imported using ODBC and JDBC cannot be modified.

 

However, this does not mean that a list of numbers imported as text cannot be converted to numerical values and used in computations. You can convert text to numerical values in queries (and in the Record Source View for reports and labels) using the Convert SQL function.

 

To do so, enter the Convert SQL function into a new query field as shown below:

 

query_sql_function

 

Other valid data types (besides DOUBLE, which is used in the above example) are:

 

•INTEGER | INT

•DOUBLE [PRECISION] | FLOAT

•VARCHAR

•VARCHAR_IGNORECASE

•CHAR | CHARACTER

•LONGVARCHAR

•DATE

•TIME

•TIMESTAMP | DATETIME

•DECIMAL

•NUMERIC

•BIT

•TINYINT

•SMALLINT

•BIGINT

•REAL

•BINARY

•VARBINARY

•LONGVARBINARY

•OTHER | OBJECT