Running a report that includes many variables on many tables (60 or more) causes SQL Exception "Unknown column 'sc_item_option.value' in 'field list'"
Steps to Reproduce
- Navigate to Reports > Create New.
- Select the Data table "Requested Item [sc_req_item]".
- Customize the columns by selecting Variables in the Columns Available.
- Click the + sign that appears above the < and > arrows.
- In the Catalog Items window, search for and select 'DG - Data Quality Issue Form'.
- Select and add all the variables for DG - Data Quality Issue Form (approximately 75 variables).
- URL: /item_option_new_list.do?sysparm_query=cat_item!%3DNULL%5EGOTOcat_item.nameLIKEDG%5Ecat_item%3Dcce6f898ac327100aca2265fc8d82cdf%5Eactive%3Dtrue
- Add a condition so fewer records are retrieved, for example, opened by 'Guest'.
- Run the report.
- Message is shown: "Syntax Error or Access Rule Violation detected by database (Unknown column 'sc_item_option.value' in 'field list')".
After carefully considering the severity and frequency of the issue, and risk of attempting a fix, it has been decided to not address this issue in any current or future releases. We do not make this decision lightly, and we apologize for any inconvenience. If you have any questions regarding this problem, contact ServiceNow Technical Support.
Perhaps this workaround can provide temporary relief:
- Create multiple reports, each of which has JOINs from fewer than 60 tables. These will export successfully.
Note: 1 variable = 1 join
Related Problem: PRB675410