Information Link fails to load with error "Failed to execute query: Incorrect syntax near '<'"
book
Article ID: KB0081338
calendar_today
Updated On:
Products
Versions
Spotfire Analyst
All Versions
Description
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.
Issue/Introduction
Information link execution may fail with the error message "Incorrect syntax near '<'" if it has more than one '' in Information Link SQL.