Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-02 |
|
|
View PDF |
A text expression evaluates to data with the TEXT,
NTEXT,
or ID
data type. Text expressions can be any combination of the following:
Text literals. For example, 'Boston'
or 'Current
Sales
Report'
Text dimensions. For example, district
or month
Text variables or formulas. For example, product.name
Functions that yield text results. For example, JOINLINES('Product:
' product.name)
Oracle OLAP supports text expressions in all languages that you can identify using the NLS_LANGUAGE option. It also supports multi-language programs and applications using a language dimension.
When you use a DATETIME
value where a text value (TEXT,
NTEXT,
or ID
) is expected, or when you store a DATETIME
value in a text variable, then the DATETIME
value is automatically converted to a text value.
The format of a DATETIME
value is controlled by the NLS_DATE_FORMAT option. Once a DATETIME
value is stored in a text variable, the NLS_DATE_FORMAT setting has no impact.
TEXT
and NTEXT
data are interchangeable in most cases. However, implicit conversion can occur, such as when an NTEXT
value is assigned to a TEXT
variable. When TEXT
is converted to NTEXT
, no data loss occurs because the UTF-8 character encoding of the NTEXT
data type encompasses most other data types. However, when NTEXT
is converted to TEXT
, data loss occurs when NTEXT
characters are not represented in the workspace character set.
When TEXT
and NTEXT
values are used together, for example in a call to the JOINCHARS
function, the TEXT
value is converted to NTEXT
and an NTEXT
value is returned.
You can embed quoted strings within a quoted string, which is necessary when you want to specify the base dimension value of a composite or conjoint dimension or when a value includes an apostrophe. Since a single quotation mark is used in Oracle OLAP to indicate a text string, it is considered a special character when used within such a string. Consequently, to specify the literal value of a single quotation mark within a text string, precede the quotation mark with a backslash.
For example, suppose you want to find out if New
York
and Apple
Sauce
are a valid combination of base dimension values in the markprod
conjoint dimension. The following statement produces the answer YES or NO
.
SHOW ISVALUE(markprod, '<\'New York\' \'Apple Sauce\'>')
When embedded quoted strings have a further level of embedding, you must use backslashes before each special character, such as the apostrophe and the backslash that must precede it in "Joe's Deli," as shown in the following statement.
SHOW ISVALUE(markprod, '<\'Joe\\\'s Deli\' \'Apple Sauce\'>')