My Oracle Support Banner

An Analysis with a Column Expression with CASE and TRENDLINE Functions Fails in Oracle Analytics with Internal error: Invalid Derived Column Reference (Doc ID 2781639.1)

Last updated on JUNE 07, 2021

Applies to:

Oracle Analytics Server - Version 5.5.0 to 5.9.0 [Release 5]
Information in this document applies to any platform.


After upgrading from OBIEE to Oracle Analytics Server, any analysis with the TRENDLINE function wrapped in a CASE statement now fails with the following error:

[nQSError: 46043] Internal error: invalid derived column reference, File server/Query/Optimizer/Request/Src/SQORRqExpr.cpp, line 1850.

See a simplified example below:

1. Create an Analysis with a Date attribute column (For example: "Time"."T00 Calendar Date") and a Measure column (For example:"Base Facts"."7- Shipped Amount")
2. Create a column with the TRENDLINE function. For example: TRENDLINE ("Base Facts"."7- Shipped Amount", ("Time"."T00 Calendar Date"), 'EXPONENTIAL', 'VALUE')
3. Add the columns to a Graph view
4. Run the report and see it is successful
5. Modify the column formula to include a CASE function. For example: CASE WHEN 4 > 2 THEN TRENDLINE ("Base Facts"."7- Shipped Amount", ("Time"."T00 Calendar Date"), 'EXPONENTIAL', 'VALUE') END
6. Run the report and see it fails with the above error


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

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