Information Link fails to load with error "Failed to execute query: Incorrect syntax near '<'"

Product:TIBCO Spotfire Analyst
Versions:All Versions

Summary:
Information link execution may fail with the error message "Incorrect syntax near '<'" if it has more than one '' in Information Link SQL.

Details:
Information link execution may fail with the error message "Incorrect syntax near '<'" if it has more than one '<conditions>' in the modified Information Link SQL.

The following is one example where you might need multiple <conditions>. In this example there is a modified SQL of an Information Link with a UNION of multiple SELECT statements, each with a WHERE <conditions> clause in order to limit both SELECT statement results.

SELECT
   e1."id" AS "ID",
FROM
   "master"."dbo"."emp" e1
WHERE
  <conditions>

UNION

SELECT
   e1."id" AS "ID",
FROM
   "master"."dbo"."emp1" e1
WHERE
 <conditions>

See the sql.log file in <Spotfire Server Installation Directory>/tomcat/logs for the actual run-time query sent to the database.

Resolution:
The '<conditions>' in the WHERE clause of Information Link SQL is required, and is automatically replaced with the run-time conditions by Spotfire upon execution. Having multiple '<conditions>' in the WHERE clause of information link is not supported. Only the first '<conditions>' is removed in the run-time query being formed.

To avoid this issue in the example above, instead create two separate Information Links. Then add the first Information Link to your analysis, and then use Insert > Rows from the second Information Link. This will give the desired results as per the original query, but will not have the syntax errors caused by the modified SQL.