Troubleshooting SQL expressions

The Troubleshooting information describes how to diagnose and solve issues with SQL expressions.

The query returns no values or displays the message: “There are no columns to display. Please adjust your search criteria or applied filters”.

Verify in OneView that at least one data object of this type is received. It must contain data with property names matching those in the Data Catalog and your query.

For DSV objects, verify that the object payload has a single row of column headers. The column headers must match the metadata property names. Property names are case-sensitive.

If the query contains the locale function, then verify that the locale keyword is lowercase. The maximum number of locale positions is 32.

The query returns the error: Unable to retrieve data…Invalid property name reference: ‘property name’

The property names reference the property names that are used in the query. Verify that property names exactly match the property names defined for the object in the Data Catalog. Property names are case-sensitive.

For DSV objects, verify that the object payload has a single row of column headers. The column headers must match the metadata property names. Property names are case-sensitive.

The query returns the error: Object ‘object name’ not found.

The object refers to the table name that is referenced in the query. Verify that the object name is correct and verify that the object name matches the name that is defined in the Data Catalog. Object names are not case-sensitive.

The lastModified predicate in the WHERE clause is not working.

The keyword lastModified is case-sensitive with an upper-case M for Modified. The timestamp must be formatted using the ISO-8601 timestamp format. Timestamps must be provided using UTC time zone conventions and include a fractional millisecond. The timestamp format is: yyyy-MMdd'T'HH:mm:ss.SSS'Z'

Example: 2021-03-17T09:30:00.001Z

The lastModified in the WHERE clause returns an error 500

The timestamp value must contain milliseconds.

At least two columns are required to configure 'dataobject query' object

This error is displayed when the query runs.

In Birst, the SELECT statement must query a minimum of two properties.

Exception occurred when executing SQL query: Property predicate is not supported.

The SELECT statement cannot include a property in the WHERE clause.

Invalid UTF-8 start byte 0x9b” is displayed during Birst data import.

A data object can be used in a query and return the correct results. The import into Birst fails with error:

Invalid UTF-8 start byte 0x9b.

The error happens because the source data objects are not in UTF-8 format. The solution is to format the source objects as UTF-8 and send them to Data Lake again.

To omit theData Lake object from a query, mark the object as corrupt. Corrupt objects are automatically excluded from queries. See Retrieving Data Objects for information to mark Data Lake objects as corrupt.

Alternatively, use the lastModified property to avoid the invalid object.

A SQL parsing error is displayed when retrieving data and object and property names contain special characters, such as hyphens or slashes.

The property names with special characters, such as hyphen (-) and slash characters (/,\), must be enclosed within double quotes.

Query fails if the lastModified predicate uses > or < lastModified. Replace with >= or <=.

A query fails if Data Lake has over 10,000 data objects to include in the query results. Update the query predicate to use WHERE lastModified >= timestamp value or WHERE lastModified <= timestamp value.

The query returns null values for integer, big integer, or decimal data types.

The query results are returned in the data type defined in the object’s metadata, which is stored in the Data Catalog. For example, if the metadata property is defined as an integer data type, then the data type returned is integer. If the data object contains a different data type, such as a decimal value, then the query returns a null value. If a null is returned, then verify that the data object’s value matches the metadata data type. If not, then you can update the metadata definition in the Data Catalog to suit the payload values.

Unable to retrieve data for a request.

A data object that is selected by the query is invalid because the object is incomplete or invalid.

For JSON objects, ensure that each JSON tuple in the data object is complete.

For DSV objects used by the JDBC driver for Birst, the payload object cannot contain extra rows with only carriage return, line feed or carriage return and line feed characters. An error is displayed, stating that the line contains a different number of fields than the header.

The message includes the Data Object ID, which is the unique identifier for a Data Lake object. Exclude the data object from the query or mark the data object as corrupt. Data objects marked as corrupt are automatically excluded from the results. Use the Data Object ID value to retrieve and review the data object through Infor OS.

The locale function returned localized values, but the values are unexpected.

Verify that the Data Catalog Locale Selections are populated. The driver uses a default locale list when no locale selections are available in the Data Catalog. You can select the Data Catalog locale selections and use the Data Catalog Locale Selections function to add or update locale selections.

See the "Locale Selections" in the Infor ION User Guide.

The query is hanging. No errors and no data are returned.

Verify that the environment running Birst Connect 2.0 agent has Java version 1.8 or later.