Skip to Main Content

Alma Analytics Resource Guide

This guide covers how to use the Alma Analytics platform to produce robust reports. Included are instructions on accessing the Oracle-based service as well as how to use its menu driven service to build custom reports.

Concatenations: Concatenate columns and rows in Alma Analytics

by Hisham Makki on October 3rd, 2023 in Analytics | 0 Comments

Concatenations

[kənˌkadəˈnāSHən]

Author: Hisham Makki, Assistant Director for Data Quality.
Required knowledge: Advanced Analytics.

Contents:

 

 
 
Concatenation combines string data values where each value is joined together in one string value. You need to convert non-string data values to string values before concatenation. The easiest way to convert data types is to use CAST function. Oracle supports ANSI string data types plus a set of built-in data types such as: CHAR , VARCHAR2 ,NCHAR , and NVARCHAR2.Please notice that the built-in data types have limitations on size.
Data Type Description *
CHAR [(size [BYTE | CHAR])]

Fixed-length character data of length size bytes or characters. Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte.

BYTE and CHAR have the same semantics as for VARCHAR2.
VARCHAR2 [(size [BYTE | CHAR])]

Variable-length character string having maximum length size bytes or characters. You must specify size for VARCHAR2. Minimum size is 1 byte or 1 character. Maximum size is:

  • 32767 bytes or characters if MAX_STRING_SIZE = EXTENDED

  • 4000 bytes or characters if MAX_STRING_SIZE = STANDARD

Refer to Oracle Database SQL Language Reference for more information on the MAX_STRING_SIZE initialization parameter.

BYTE indicates that the column will have byte length semantics. CHAR indicates that the column will have character semantics.
NCHAR [(size)]

Fixed-length character data of length size bytes or characters. Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte.

BYTE and CHAR have the same semantics as for VARCHAR2.
NVARCHAR2 [(size)]

Variable-length Unicode character string having maximum length size characters. You must specify size for NVARCHAR2. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of:

  • 32767 bytes if MAX_STRING_SIZE = EXTENDED

  • 4000 bytes if MAX_STRING_SIZE = STANDARD

Refer to Oracle Database SQL Language Reference for more information on the MAX_STRING_SIZE initialization parameter.

 

Columns Concatenation

    There are two methods we can use concatenate columns:

  • Concatenation function CONCAT

  • Concatenation operator ||

The CONCAT() function accepts, only, two arguments whose data types can by any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. However, the concatenation operator allows concatenating more than two strings in a more readable statement. So, I'm using the operator only on this page :-)  

String || String || String

Examples:

Column name || Column name

"Bibliographic Details"."Publication Place" || ' ' || "Bibliographic Details"."Publisher" || ', ' || "Bibliographic Details"."Publication Date"

Results:

Albany, N.Y. : Denver, Colo. : Books and Open-File Reports distributor; Dept of the Interior US Geological Survey, 1988.

Albany, N.Y. : State University of New York Press, c1997.

Albany, N.Y. : University of the State of New York State Education Dept Bureau of Curriculum Development, 1985.

Example of simple SQL statement:

SELECT 
   "Bibliographic Details"."Publication Place" || ' ' || "Bibliographic Details"."Publisher" || ', ' || "Bibliographic Details"."Publication Date" saw_0
 FROM "Physical Items"
 WHERE 
"Item Creation Date"."Item Creation Year" > '2020'

 

Rows Concatenation

 

Without further ado, the straightforward way to concatenate rows is to use LISTAGG function. However, LISTAGG function is not available in Alma Analytics. In order to use LISTAGG function, we need to use an embedded database function to run LISTAGG. The embedded DB functions directly call database functions from Oracle BI. We will use EVALUATE_AGGR function to embed LISTAGG in the report.

  • EVALUATE_AGGR function: "Passes the specified database function with (optional) referenced columns as parameters to the back-end data source for evaluation. This function is intended for aggregate functions with a GROUP BY clause. The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function."  (Copied)
    Syntax: EVALUATE_AGGR('db_agg_function(%1...%N)' [AS datatype] [, column1, columnN])

  • LISTAGG function: LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column.
    Syntax: LISTAGG ( measure_expr [, delimiter]) WITHIN GROUP (order_by_clause) [OVER query_partition_clause]

Here is the statement I used to combine all rows of library codes in one row. This is the only statement that works for the NZ.

EVALUATE_AGGR('LISTAGG(DISTINCT  %1,%2 ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY %3)' as varchar(550), "Library Unit"."Library Code (Active)",',',"Library Unit"."Library Code (Active)")

statement Description
EVALUATE_AGGR The function to embed an aggregate DB function.
LISTAGG The function to concatenate rows.
DISTINCT Limit values to distinct values to eliminate duplicates.
%1,%2 %1 refers to the first column ("Library Unit"."Library Code (Active)")
%2 refers to the delimiter ','.
ON OVERFLOW TRUNCATE Returns a truncated list of values if the size of returned list is larger than the specified size.
ORDER BY %3 order by the third column in the statement ("Library Unit"."Library Code (Active)")
as varchar(550) Convert returned values to varchar. Size is optional.

Example of simple SQL statement:

SELECT 
   "Institution"."Institution Name" saw_0,
   EVALUATE_AGGR('LISTAGG(DISTINCT  %1,%2 ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY %3)' as varchar(550), "Library Unit"."Library Code (Active)",',',"Library Unit"."Library Code (Active)") saw_1,
   "Physical Item Details"."Num of Items (In Repository)" saw_2,
   DESCRIPTOR_IDOF("Physical Items"."Institution"."Institution Name") saw_3
 FROM "Physical Items"
 WHERE 
"Item Creation Date"."Item Creation Year" > '2020'

 

Tip 😎 You can concatenate columns and rows in one statement:

Using System Events subject area, the following example concatenates two columns ("Event Key Name" and "Event Key Value"), and the rows of concatenated columns.

EVALUATE_AGGR('LISTAGG(DISTINCT  %1,%2 ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY %3)' as varchar(550), ("Event Details"."Event Key Name" || ' : ' || "Event Details"."Event Key Value"), ' - ', "Event Details"."Event Key Value")

 

 Add a Comment

0 Comments.

  Search this Blog


  Recent Posts


Blog Table of Contents
Ad Hoc Reports On-The-Fly: A Dream Fulfilled
How to Join two subject areas in Data Visualization
Date-Time Functions in a Nutshell
Concatenations: Concatenate columns and rows in Alma Analytics

  Subscribe


Enter your e-mail address to receive notifications of new posts by e-mail.


  Archive


  Subjects


Analytics
Reports
  Return to Blog
This post is closed for further discussion.