|Oracle® OLAP DML Reference
10g Release 1 (10.1)
Part Number B10339-02
TEXT or NTEXT
This function accepts TEXT values and NTEXT values as arguments. The data type of the return value depends on the data type of the values specified for the arguments:
When all arguments are TEXT values, the return value is TEXT.
When all arguments are NTEXT values, the return value is NTEXT.
When the arguments include both TEXT and NTEXT values, the function converts all TEXT values to NTEXT before performing the function operation, and the return value is NTEXT.
An expression whose lines JOINCOLS joins with those of next-expression. When the expression has a data type other than TEXT or NTEXT, JOINCOLS converts it to TEXT. See "Converting Expressions That Are not TEXT or NTEXT".
One or more expressions to join with first-expression. When an expression you want to concatenate has a data type other than TEXT or NTEXT, JOINCOLS converts it to TEXT. See "Converting Expressions That Are not TEXT or NTEXT".
When the data type of an expression is not TEXT or NTEXT, JOINCOLS automatically converts its value to TEXT before concatenating it with the other values. So when you put a number in a JOINCOLS function, the number is automatically converted to TEXT. The format of the result depends on the settings of the COMMAS and DECIMALS options.
JOINCOLS ignores any arguments that have a value of
The number of lines in the return value is always the same as that in the argument expression that has the most lines. When a given argument expression has fewer lines, JOINCOLS repeats its last line in each subsequent line of the return value. This repeating feature is useful when an argument expression is a single-line separator, such as a space or hyphen. See Example 15-27, "Joining the Columns of Two Text Expressions".
A single concatenated line cannot exceed 498 bytes.
Example 15-27 Joining the Columns of Two Text Expressions
In the following example, each line in
citylist is joined with a quoted text value, and the corresponding line from
citylist has the following values.
Boston Houston Chicago Denver
cityrep has the following values.
Brady Lopez Alfonso Cody
The JOINCOLS function
JOINCOLS(citylist ' -- ' cityreps)
returns the following.
Boston -- Brady Houston -- Lopez Chicago -- Alfonso Denver -- Cody