|Oracle® OLAP DML Reference
10g Release 1 (10.1)
Part Number B10339-02
The return value depends on the value of the type argument.
CONVERT(expression, type [argument...])
The expression or variable to be converted.
The type of data to which you want to convert expression. The keywords that represent the types are described in Table 9-2, "Keywords for the type Argument of the CONVERT Function".
Table 9-2 Keywords for the type Argument of the CONVERT Function
|TEXT||Conversion to standard Oracle OLAP data types. Corresponds to CHAR and VARCHAR2 data types in the Oracle relational database. A TEXT character is encoded in the database character set.|
|NTEXT||Conversion to standard Oracle OLAP data types. Corresponds to the NCHAR and NVARCHAR2 data types of the relational database. An NTEXT character is encoded in |
|ID||Conversion to standard Oracle OLAP data types.|
|DATE||Conversion to standard Oracle OLAP data types.|
|NUMBER||Conversion to standard Oracle OLAP data types.|
|BOOLEAN||Conversion to standard Oracle OLAP data types.|
|INTEGER||Conversion to standard Oracle OLAP data types.|
|SHORTINTEGER||Conversion to standard Oracle OLAP data types.|
|LONGINTEGER||Conversion to standard Oracle OLAP data types.|
|DECIMAL||Conversion to standard Oracle OLAP data types.|
|SHORTDECIMAL||Conversion to standard Oracle OLAP data types.|
|DATETIME||Conversion to standard Oracle OLAP data types.|
|BYTE||Converts a single character into an ASCII integer value in the range 0 to 255. Or BYTE converts an INTEGER within this range into a character. An INTEGER outside this range is taken modulo 256 and then converted; that is, 256 is subtracted from the INTEGER until the remainder is less than 256, and that within-range remainder is then converted into a character.|
|INFILE||Encloses an ID, TEXT, DATE, or RELATION value within single quotes, so that it can be read with an INFILE command. This means that expression must have TEXT, ID, DATE, or RELATION values. In the case of TEXT values with no alphanumeric equivalent, INFILE converts them to the correct escape sequences.|
|PACKED||Converts a number to a decimal value and then to packed format -- a text value 8 bytes long containing 15 digits and a plus or minus sign. Fractions cannot be represented in packed numbers; therefore the conversion process rounds decimal numbers to the nearest integer. See "PACKED and BINARY Conversion".|
|BINARY||Does not indicate conversion to a standard Oracle data type but allows additional conversion capabilities. BINARY does no conversion. The internal representation of every value, regardless of data type, is returned as a text value. For TEXT data types, the result will be the value itself and will therefore be of variable length. For ID and DECIMAL data types, the result will be 8 bytes long; ID values will be blank filled, when necessary. For BOOLEAN or INTEGER, the default result will be 2 or 4 bytes long respectively (see the arguments explanation for an additional argument that lets you vary the width slightly). For all other data types, the result will be 4 bytes long. See "PACKED and BINARY Conversion".|
When you specify TEXT, NTEXT, ID, DATE, or INFILE for the type, you can specify additional arguments to determine how the conversion should be done:
Numeric values to TEXT values
TEXT [decimal-int|DECIMALS [comma-bool|COMMAS [paren-bool|PARENS]]]
Numeric values to NTEXT values
NTEXT [decimal-int|DECIMALS [comma-bool|COMMAS [paren-bool|PARENS]]]
Numeric values to ID values
DATE values to TEXT, NTEXT, or ID values
A DATE value or the values of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR with VNF
ID [dwmqy-dimension]|TEXT [dwmqy-dimension|'vnf']
TEXT, NTEXT, or ID values to DATE values
INFILE [width-exp|LSIZE [escape-int|0]]
When specifying BINARY with BOOLEAN or INTEGER data
Onverting between TEXT and NTEXT values
An INTEGER expression that controls the number of decimal places to be used when converting numeric data to TEXT or ID values. When this argument is omitted, CONVERT uses the current value of the DECIMALS option (the default is 2).
A Boolean expression that determines whether commas are used to mark thousands and millions in the text representation of the numeric data. When the value of the expression is
YES, commas are used. When this argument is omitted, CONVERT uses the current value of the COMMAS option (the default is
A Boolean expression that determines whether negative values are enclosed in parentheses in the text representation of the numeric data. When the value of the expression is
YES, parentheses are used; when the value is
NO, a minus sign precedes negative values. When this argument is omitted, CONVERT uses the current value of the PARENS option (the default is
A text expression that specifies the template to use when converting a DATE expression to text. The template can include format specifications for any of the four components of a date (day, month, year, and day of the week). Each component in the template must be preceded by a left angle bracket (
<)and followed by a right angle bracket (
>). You can include additional text before, after, or between the components.
The valid formats for each date component are the same as the formats allowed in the DATEFORMAT option.
In the following statement, CONVERT returns today's date as a text value that is formatted by a date-format argument.
SHOW CONVERT(TODAY TEXT '<MM>-<DD>-<YY>')
In this example, today's date is March 31, 1998, and the SHOW command presents it in the following format.
When you do not include the date-format argument, the format of the result is determined by the current setting of the DATEFORMAT option.
The name of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. Oracle OLAP uses the VNF of dwmqy-dimension when converting a DATE value to a TEXT or an ID value. When you have not specified the VNF of dwmqy-dimension, Oracle OLAP uses its default VNF.
In the following statement, CONVERT returns today's date as a text value that is formatted by the VNF of the YEAR dimension.
show convert(today text year)
In this example, today's date is March 31, 1998, and the SHOW command presents it in the following format.
A text template that specifies the value name format to use when converting values of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR to text. The template can include format specifications for any of the components of a time period. Time period components include all the components of a date (day, month, year, and day of the week), plus the fiscal year and period components. The template can also include the name of the DAY, WEEK, MONTH, QUARTER, or YEAR dimension as a component. Each component in the template must be preceded by a left angle bracket and followed by a right angle bracket. You can include additional text before, after, or between the components.
The vnf argument to the CONVERT function is similar to the template in a VNF command. However, a VNF command template must be designed for precise and unambiguous interpretation of input, while the vnf argument is not so constrained. Therefore, the format styles allowed in the vnf argument are more extensive than those allowed in a VNF command template.
Valid format styles for a vnf argument include all the format styles allowed in the template of a VNF command, plus all the format styles allowed in a DATEFORMAT template. DATEFORMAT provides the following format styles that are not allowed in VNF command templates but that are valid in the vnf argument to the CONVERT function:
Ordinal styles for the day of the month (DT and DTL)
First-letter style for the month (MT)
Styles for the day of the week (W, WT, WTXT, WTXTL, WTEXT, and WTEXTL)
Append a B code to any of these formats to indicate that you want to display the beginning day or month of the period, rather than the final day or month.
You can use any combination of VNF and DATEFORMAT format styles with for any dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. This contrasts with the template in a VNF command, in which only certain format combinations are valid for each type of dimension.
In the following statement, CONVERT returns the current value of the MONTH dimension as a text value that is formatted by a vnf argument.
SHOW CONVERT(month TEXT '<MTEXTL>, <YYYY>')
In this example, the first MONTH value in status is DEC97, and the SHOW command presents it in the following format.
When you do not include the vnf argument, the format of the result is determined by the VNF of the dimension whose values you are converting. When the dimension has no VNF, the result is formatted according to the default VNF for the type of dimension being converted.
A text expression that specifies how to interpret the specified text value as a DATE value when the order of the text value's components (month, day, and year) is ambiguous. The expression can be one of the following:
'DYM'. Each letter represents a component of the date:
M stands for month,
D stands for day, and
Y stands for year.
The name of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR whose VNF or default date-order determines how to interpret the specified text value as a DATE value when the order of the text value's components is ambiguous.
The following examples show how you can control the conversion of a text expression by using a date-order or dwmqy-dimname argument.
The following statements use the date-order argument.
SHOW CONVERT('1/3/98' DATE 'MDY') SHOW CONVERT('1/3/98' DATE 'DMY')
These statements produce the following output.
The following statement uses the dwmqy-dimname argument. It specifies the
qrtr dimension, which was defined as a dimension of type QUARTER.
show convert('96-2' date qrtr)
The statement produces the following output.
The following statement also uses the dwmqy-dimname argument. It specifies the
fyear dimension, which has the following definition.
DEFINE fyear DIMENSION YEAR ENDING JUNE VNF 'TY<ff>'
SHOW CONVERT('jan97' DATE fyear)
produces the following output.
When you do not include the date-order or dwmqy-dimname argument, any ambiguity in the interpretation of a text expression is resolved by the current setting of the DATEORDER option. Refer to the DATEORDER option for a complete description of DATE values and how they are interpreted.
An INTEGER expression that indicates the width of the output from CONVERT. The minimum width is 7. The default width is the current value of the LSIZE option. This argument is required when you specify the escape-int argument.
Indicates whether escape sequences are to be used in the output. For this argument you can specify one of the values listed in Table 9-3, "Values for escape-int Argument of the CONVERT".
Table 9-3 Values for escape-int Argument of the CONVERT
|-1||Do not use escapes. Precede -1 with a comma (|
|0||Use escapes for unprintable characters. (Default)|
|1||Use escapes for all characters.|
For more information on escape sequences in the OLAP DML, see "Escape Sequences".
An INTEGER expression that controls the width of the converted result. It can evaluate to
4 bytes. The default width is
2 for BOOLEAN, or
4 for INTEGER. When an integer is too large to fit in the specified width, the result is
NA. When the width is invalid or specified for some other data type, an error occurs.
A keyword indicating that no character set conversion should be performed. Instead, Oracle OLAP only tags the converted value with the target data type, leaving the data as it was before the CONVERT function was called. Use this keyword only when it is necessary to store binary data in a TEXT or NTEXT variable.
The maximum number of characters in a line is 4000. An error occurs when you try an INFILE conversion that produces a line with more than 4000 characters. This can occur when the source line exceeds 99 characters and enough of them need escape sequences.
When you convert a DATE value to an INTEGER value, the result is the sequence number that represents the date (the sequence number
1 represents January 1, 1900). When you convert a DATE value to another numeric type, the date's integer sequence number is converted to the specified numeric data type.
When you convert an INTEGER value to a DATE value, the result is the date whose sequence number matches the specified integer (January 1, 1900 is represented by the sequence number 1). When you convert from another numeric type to a DATE value, the number is converted to an INTEGER, then the INTEGER is converted to a DATE value.
When you convert a value of a dimension of type DATE, DAY, WEEK, MONTH, QUARTER, or YEAR to an ID value, and the result is more than eight characters long, the result is truncated.
When you convert a given value of a relation into an INTEGER value, the result represents the position of the value in the relation's dimension. This behavior reflects the fact that the values of a relation are dimension values, not TEXT values.
When you convert a number to a DATE value and the result is outside the range of valid dates, CONVERT returns
NA. Valid dates range from January 1, 1900 (sequence number 1) to December 31, 9999 (sequence number 2,958,464).
When you try to convert a number larger than 2,147,483,647 or smaller than -2,147,483,647 (the maximum and minimum integer values), to an INTEGER, you get a result of
Likewise, when you try to convert a number larger than 32,767 or smaller than -32,768 to a SHORTINTEGER, you get a result of
NA. For a value of type DATE, the integer 32,767 represents the date September 17, 1992. Therefore, CONVERT returns
NA when you attempt to convert any date later than this to a SHORTINTEGER value.
When you convert a null string to a BYTE, you get a result of
32. CONVERT returns the same value for a null string (
'') as it does for a blank string (
The PACKED and BINARY types are useful for creating binary files that contain PACKED and BINARY data. To create such a file, use FILEOPEN statement with the BINARY keyword to open the file and FILEPUT to write values to it. You can use the ROW function as an argument to the FILEPUT command to help format the file.
Example 9-21 Converting Decimal Values to Text
This example shows how to use the JOINCHARS and CONVERT functions together to combine some text with the value of the variable
price for a product and month, and show the price without decimal places.
LIMIT month TO 'Jul96' LIMIT product to 'Canoes' SHOW JOINCHARS('Price of Canoes = $' CONVERT(price TEXT 0)) Price of Canoes = $200
Example 9-22 Converting Text Values to Escape Sequences
This example shows how to use the CONVERT function with the ESCAPEBASE option to convert a TEXT value from its default decimal escape sequences to hexadecimal escape sequences.
DEFINE textvar VARIABLE TEXT textvar = 'testvalue' SHOW CONVERT(textvar INFILE 9 1) '\d116\d101\d115\d116\d118\d097\d108\d117\d101' ESCAPEBASE = 'x' SHOW CONVERT(textvar INFILE 9 1) '\x74\x65\x73\x74\x76\x61\x6C\x75\x65'