Collapse Submit a Service Request Contact Information for Technical SupportMy Service Notifications
Automatic Joins in reports can cause failures
Answer ID 9139 | Last Review Date 03/18/2019
Why does adding certain columns cause a report to fail?
Reports, All versions
Oracle B2C Service, (OSvC)
A report cannot run when certain columns are added to the definition, even though the columns come from tables already in the report.
When a report is run, certain columns in some tables automatically generate an additional join behind the scenes. For example, the column incidents.assgn_acct_id is an integer field. However, when it is used in a report, the system automatically joins the report to the accounts table in order to display the name of each agent, rather than their arbitrary id number. This is a feature within the product designed for convenience and to promote usability. If a report is poorly optimized or already close to the defined limitations within analytics, this additional join could cause the report to fail (max_join).
If an identifier is needed, the string ".id" can be appended to the column. From the above example, that would be incidents.assgn_acct_id.id. Please note, it is rare that an appropriately optimized report would max join solely for this reason and there are often other options that have a far greater impact on the data set being returned.