|Oracle® OLAP DML Reference
11g Release 2 (11.2)
Part Number E17122-05
The JOINCOLS function joins the corresponding lines of two or more multiline text values. The function returns a multiline text value composed of the concatenated lines up to a length of 498 bytes (the maximum length of a single concatenated line).
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 7-137, "Joining the Columns of Two Text Expressions".
TEXT or NTEXT
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. JOINCOLS ignores any arguments that have a value of
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. JOINCOLS ignores any arguments that have a value of
Example 7-137 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