One of these errors is appearing as an error in the UI or in the application logs:
- '*** ERROR *** WhereClause - invalid token'
- 'Syntax Error or Access Rule Violation detected by database: Unknown column 'prefix_u_field' in 'on clause')" is appearing in the application logs (where 'prefix_u_field' is present in one of the where/join clauses of the database view)
- Where clause in view 'view_name' has an invalid field or a field that is not visible (SUBSTRING_INDEX)
Release or Environment
London and later.
The first error is produced when there is an unexpected character in the 'Where Clause' of a 'sys_db_view_table' record.
The presence of a "." in a join table's where clause suppresses any further parsing of the condition. This means that the 'prefix_field' syntax used in the join/where condition is not parsed and is interpreted as a literal field name, which does not exist, leading to the second error being reported.
The last error is caused because a SQL function has been introduced into the where/join clause and the presence of the '_' in the name is being parsed and being interpreted as if it was a 'prefix_field' combination, leading to the error as there is no view table with that prefix. The use of SQL functions in the join/where clause is not documented as supported and their usage could cause the view not to execute.
=, !=, <, <=, >, >=, &&, ||
More information is available here: https://docs.servicenow.com/bundle/madrid-platform-administration/page/use/reporting/task/t_AddATableToTheDatabaseView.html