Jump to content

Can I use dynamic query on Spotfire?

Junghwan Kim

Recommended Posts


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 


  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"


  "spotfire_server"."public".?DynamicTable s1




(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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...