Cause:
According to Spotfire data source XML template specification, max-in-clause-size defines the maximum size of an SQL IN clause, causing larger lists to be split into several IN clauses that are joined by OR statements. In Spotfire 11.4.0 and 11.4.1, the generated query can be generated with syntax errors.
Queries generated in Spotfire 11.4 using Information Designer (syntax errors):
SELECT column1, column2
FROM table1
WHERE column1 IN (1,2,3,…, 1000) OR (1001,1002,1003,….,2000) OR (2001, 2002,2003…,3000);
Queries generated in Spotfire 11.4 using Information Designer (correct syntax):
SELECT column1, column2
FROM table1
WHERE column1 IN (1,2,3,…, 1000) OR column1 IN (1001,1002,1003,….,2000) OR column1 IN (2001, 2002,2003…,3000);
Resolution:
This is a known defect in Spotfire Server version 11.4.0 and 11.4.1. It was fixed in version 11.4.2:
TSS-29413 - For Information Services, an invalid query is generated when the IN clause size is greater than 1000. This results in errors when, for example, using marking.
https://docs.tibco.com/pub/spotfire_server/11.4.5/TIB_sfire_server_11.4.5_relnotes.pdf?id=0
If an upgrade to 11.4.2 is not an option, there is also a workaround solution that can be used. It involves adjusting <max-in-clause-size> and <condition-list-threshold> parameters in the Data Source template that is being used. Finding values that are high enough should allow Spotfire to stop using OR condition after every set number of values and, instead, use the temp table to join the original table. But, since this is an inner join with temp table, it has some negative performance effect.
<max-in-clause-size>100000</max-in-clause-size>
<condition-list-threshold>100000</condition-list-threshold>
Note: 100000 value is used here only as an example. You could try starting with these values. If issue still exists, try increasing the values further, until you reach some number that doesn't cause errors.