Hi all,
We've recently migrated someone from Apex 21.2 to Apex 22.2 and found a bug in how Interactive Reports generate their SQL.
Feels like a very specific trigger but it came up in one our saved default report settings and a user could also induce it using the options presented by the IR. If they did I don't believe they could fix it (as in get a working alternative) by themselves in all cases.
TL;DR - Make sure source queries/tables/views use column names that don't require double quotes to reference them in SQL.
I've setup a public demo app here on Apex 23.1: https://apex.oracle.com/pls/apex/r/flachm/ir-compute-highlight-test/home?session=7429133658436
If you toggle the Compute Column = Y highlight on then the report fails with the error: ORA-00920: invalid relational operator.
In Apex 22.2 the error we get is: ORA-00905 missing keyword.
To replicate the issue:
- Create an Interactive Report and in it's source, either through aliasing or when the table/view is constructed, have a column name that requires double quotes to be referenced.
- In the column settings allow Compute for the column that requires double quotes.
- In the application use the IR actions menu to create a compute column which references the column that requires double quotes.
- Apply a highlight based on the output of the compute column.
The issue will occur if the Highlight is on, and when off the IR seems fine.
In the demo app I've used some EMP data and aliased the SAL column to “Sal Ary” in the source query:
The Compute column then checks the value of “Sal Ary” is greater than 2000 and outputs Y or N:
The Direct Column Highlight applies a highlight by directly checking the same criteria that “Sal Ary” is > 2000 and this works fine.
The Compute Column = Y highlight checks the compute column value and causes the issue to occur.
When logged in as a developer, enabling Info level debugging and reviewing the generated SQL the issue can be seen where it references the Compute column expression inside the Highlight condition, Sal Ary is specified without double quotes. All other references to the column (the column itself, and the direct highlight) have double quotes:
I think there's a few option to work around this, like known Compute operations could be done in the IR's source query. Simple compute operations like this example could be done directly in the highlight, but not all compute operations can be replicated this way and a user has the control to add more compute columns and highlights if they wish to.
So the proper work around I believe is to ensure the source columns can all be referenced without double quotes and use Column Labels in the IR Configuration to display any desired presentation value.