BAM queries with time based group are failing (Doc ID 2916791.1)

Last updated on APRIL 01, 2024

Applies to:

Goal

BAM uses the column name of a data object to construct the column alias used in the query. This is typically okay, as the column name is limited to a maximum of 30 bytes. However, when a function is called on the column then a function prefix is added to the column name, resulting in a column alias longer than 30 bytes.

Below SQL is generated where column alias is greater than 30 bytes:

SELECT * FROM ( SELECT EXTRACT(DAY FROM InvoiceCompletionDate) AS DAYOFMONTH_InvoiceCompletionDate, COUNT(MyMeasure) AS COUNTMyMeasure
FROM BEAM_VIEW_61 GROUP BY EXTRACT(DAY FROM InvoiceCompletionDate) ORDER BY
EXTRACT(DAY FROM InvoiceCompletionDate) ASC ) WHERE ROWNUM <= 10240

Note that original column name is InvoiceCompletionDate (which has 21 characters) however when calling DAYOFMONTH on that column BAM concatenates the function+column_name (DAYOFMONTH_InvoiceCompletionDate) generating an identifier of 32 characters, causing ORA-00972: identifier is too long

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.