Text Expressions

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)

Language of Text Expressions

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.

Working with DATETIME Values in Text Expressions

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.

Working with NTEXT Data

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.

Using a Single Quotation Mark Within a Text Value

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\'>')