Notifications

632 views

Description

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

  1. Navigate to Reports > Create New.
  2. Select the Data table "Requested Item [sc_req_item]".
  3. Customize the columns by selecting Variables in the Columns Available.
  4. Click the + sign that appears above the < and > arrows.
  5. In the Catalog Items window, search for and select 'DG - Data Quality Issue Form'.
  6. 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
  7. Add a condition so fewer records are retrieved, for example, opened by 'Guest'.
  8. Run the report.
    • Message is shown: "Syntax Error or Access Rule Violation detected by database (Unknown column 'sc_item_option.value' in 'field list')".

Workaround

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

Seen In

Fuji Patch 13
Fuji Patch 9 Hot Fix 1
Geneva Patch 4
Geneva Patch 5
Geneva Patch 6
Geneva Patch 6 Hot Fix 2
Geneva Patch 9
Helsinki Patch 3
SR - IRM - GRC Profiles - Madrid 2019 Q2
SR - IRM - PA Premium Integration - New York 2019 Q3
SR - IRM - Policy and Compliance - Madrid 2019 Q2
SR - IRM - Risk Management - New York 2019 Q3
SR - IRM - Vendor Risk Management - Madrid 2019 Q1
SR - Security - Integration Framework - Madrid 2019 Q2
SR - Security - Support Common - Madrid 2019 Q2
SR - Security - Support Orchestration - Madrid 2019 Q2
SR - SIR - Security Incident Response - Madrid 2019 Q2
SR - SIR - Store SecOps Setup Assistant - Madrid 2019 Q2
SR - SIR - Store Threat Core - Madrid 2019 Q2
SR - SIR - Store Trusted Security Circles Client - New York 2019 Q3
SR - SIR - Threat intelligence - New York 2019 Q3
SR - SIR - VirusTotal Integration - New York 2019 Q3
SR - VR - Qualys - New York 2019 Q3
SR - VR - Vulnerability Response - New York 2019 Q3

Associated Community Threads

There is no data to report.

Article Information

Last Updated:2019-08-27 00:21:33
Published:2019-08-27