Can I use dynamic query on Spotfire?

[Junghwan Kim]

Can I use dynamic query on Spotfire?

I want to change the name of the table in FROM in an informational link.

For example:

SELECT *

FROM XXX1

SELECT *

FROM XXX20

Enter XXX1~XXX20 as parameters and apply them to FROM.

I want to execute the query in a dynamic way.

[Fredrik Rosell]

Hello,

I think your best option would be to use a stored procedure, and pass the parameter/table to use to that procedure.

---

I started testing another approach, using parameters, which I could not get working in my case (using PostgreSQL), but depending on what database your are connecting to, it may work better for you, so I'm describing that anyway, as a possible option: 

1. Edit the SQL of your Information Link, and add a parameter for the table name. In this case, I named it DynamicTable and with the prefix ? as it's a parameter so ?DynamicTable 

SELECT

  s1."spotfire_version" AS "SPOTFIREVERSION",

  s1."schema_version" AS "SCHEMAVERSION",

  s1."encryption_canary" AS "ENCRYPTIONCANARY",

  s1."cluster_id" AS "CLUSTERID",

  s1."upgrade_initiated" AS "UPGRADEINITIATED",

  s1."upgrade_info" AS "UPGRADEINFO"

FROM

  "spotfire_server"."public".?DynamicTable s1

WHERE

  <conditions>

  

(I did my test using the Spotfire database itself, where I simply duplicated the sn_version table, naming the new table sn_version2)

2. In the Information Link > Parameters, after Refresh, I now had the DynamicTable parameter, for which I set DataType: Undefined, Value Type: Single Value

3. I could now test running this information link, and for testing only in Analyst, be prompted to enter the parameter. This works well - enter the table name to use - and that is used in the query.

4. Now, to provide the parameter properly, I attempted to use a document property containing the table names as input to On-Demand on this Information Link. 

However, it turned out that you can't use a property of the Undefinied type for this (that results in the error: "Parameters with the data type 'Undefined' cannot be used for loading data on demand."), and using another type (String), resulted in incorrect sql for my database type being generated (the parameter was put in single quotes - 'tablename') and that is not configurable. So, I could not get this working in that test but it might still be an alternative for you to explore.

---

For more information on using parameterized information links, see https://docs.tibco.com/pub/sfire-analyst/12.1.1/doc/html/en-US/TIB_sfire-analyst_UsersGuide/index.htm#t=id%2Fid_parameterized_information_links.htm&rhsearch=parameter&rhhlterm=parameter&rhsyns=%20

For more information on using property controlled On-Demand, see https://docs.tibco.com/pub/sfire-analyst/12.1.1/doc/html/en-US/TIB_sfire-analyst_UsersGuide/index.htm#t=add%2Fadd_example_of_property_controlled_on_demand_data.htm&rhsearch=parameter&rhsyns=%20