Error while running modified SQL for an on-demand data table: "Could not load on-demand data for table XXX" "Failed to execute data source query."

Created
October 08, 2021 23:25

Date Posted: 2018-07-17
Product: TIBCO Spotfire®

Problem:

Error while running modified SQL for an on-demand data table: "Could not load on-demand data for table XXX" "Failed to execute data source query."

Solution:

Error while running on-demand table: "Could not load on-demand data for table XXX" "Failed to execute data source query" can be due to various reasons. When on-demand fails, the Spotfire Server sql.log file is updated with the wrong query, but in this case, there is nothing updated in the sql.log file, because the query is not getting formed. The following error appears in the Spotfire Server server.log file, instead:

ERROR 2016-03-16T00:43:47,264-0700 [userID, #119] api.common.InformationModelServiceCommon: An Internal Error has occurred. <conditions>-tag not found in the query.

Symptoms:
On-Demand fails with the following error: "Could not load on-demand data for table XXX" "Failed to execute data source query."

Or the following error appears in the server.log file:

ERROR 2016-03-16T00:43:47,264-0700 [sushank, #119] api.common.InformationModelServiceCommon: An Internal Error has occurred. <conditions>-tag not found in query.
com.spotfire.ws.api.common.InformationModelWebServiceException: An Internal Error has occurred. <conditions>-tag not found in query.

Cause:
On Demand fails because the runtime query is not being formed, due to a missing <conditions> tag in the SQL query for the on-demand table's information link.

Add the <conditions> tag in the information link SQL as shown below.

WHERE
<conditions>
or

WHERE
column1 = column2
and
<conditions>

Note: The limits on changes that can be made to an information link's auto-generated SQL query are documented in the Spotfire desktop client help topic whose title is "Editing the SQL of an Information Link", as follows:

-----------------------------

The modified SQL is not validated before execution. For this reason, you should not:

alter the name of a column (the AS part of SELECT xxx AS yyy)
alter the number of columns returned
alter the datatype of columns returned
alter the order of columns returned
remove <temporary_result_#> from the FROM-clause in information links that go against multiple data sources
remove the trailing WHERE <conditions>, as it will be replaced by any conditions applied at runtime

-----------------------------

Spotfire desktop client help topic with the title "Editing the SQL of an Information Link"

Related articles