Spotfire, Dynamic DataTables, and Temp Tables

With dynamic queries, Spotfire generates a SQL statement with an IN clause that matches the values that have been selected. Where there are too many values for the IN clause, Spotfire generates a temp table and then joins the temp table to the main query. Although I don't know the exact limit, it appears to be somewhere around 5000 - 7000 values.

Behind the scenes, Spotfire takes care of building the temp table. It then generates a query that does a join between the source table and the temp table rather than an IN clause. As long as the database user executing the query has the proper grants, everything works without issue.

If the grants aren't there, the error message that results is not very helpful. It simply says the data table could be refreshed. The failure happens immediately so that's one way to tell if there is an issue with the query being generated.

The way I discovered this was by debugging an issue with a co-worker. We traced the SQL statements as they were being issued and found the temp table being used rather than the IN clause. The following statement is an example, with modified schema and table names, of one such statement that was issued

SELECT F1."DIM_ENTITY_ID" AS "DIMIHSENTITYID",

       F1."DIM_DATE_ID" AS "DIMDATEID",

       F1."PRODUCTION_DATE" AS "PRODUCTIONDATE",

       F1."ENTITY_ID" AS "ENTITYID",

       F1."API_NUMBER" AS "APINUMBER",

       F1."LIQUID" AS "LIQUID",

       F1."GAS" AS "GAS",

       F1."WATER" AS "WATER",

       F1."ETL_LOAD_DATE" AS "ETLLOADDATE",

  FROM "MY_SCHEMA"."MY_TABLE" F1, SFTMP78830164567 S2

WHERE (F1."ENTITY_ID" = S2.VALUE)