The standard Cast function is supported, generating SQL of the form CAST (expression
AS data type
). The following data types are supported:
- CHAR (n)
- DECIMAL (m, n)
- NUMBER (*) (n) (*, n) (m, n)
- TIME (n)
- TIMESTAMP (n)
- VARCHAR (n)
When dragging a Cast operator into a variable, the following tree element is created.
Variable Creation > Input > Variables: SQL Elements pane - Other > Cast Function
Columns and/or other expressions can be moved into the (empty) branch of the tree. The data type to cast to is specified in the Properties panel. Double-click on Cast, or highlight it and click Properties. Valid data types, as listed above, are available in the pull-down.
The following figure demonstrates how to specify the data type using the pull-down and related text boxes, while specifying the desired FORMAT using the Data Type and/or Attributes text box.
A representation of the resulting CAST function (with … representing the column or expression to be cast) is given in the Resulting SQL text box below.
Variable Creation > Input > Variables: SQL Elements pane - Other > Cast Function Properties
In the following figure, the Data Type pull-down has been set to blank and the desired data type (decimal) and attribute (format) have been specified freely as text in the Data Type and/or Attributes text box.
Variable Creation > Input > Variables: SQL Elements pane - Other > Cast Function Properties (Example)
If it is necessary to specify the character set or the case specific option, consider using the SQL Text with Arguments element in the Other category. To specify the case specific option, the SQL text field may be set to CAST(<P1> AS <P2>) where the first argument is the expression being cast and the second argument is a Text literal representing the type designation, such as CHAR(30) CASESPECIFIC. To specify the character set, the SQL Text field may be set to TRANSLATE(<P1> USING <P2>) where the first argument is the Cast expression and the second argument is the Text literal representing the source_repertoire_TO_target_repertoire, such as LATIN_TO_UNICODE.