On-demand Query with an Array of Array

[Foong Kok Leong]

Hello, I would like to do an on-demand query with an "array of array" as my parameter, how can we do that

Below is how the query will look like if the parameters were hardcoded:

.....

WHERE (u.year, u.month, u.day, c.year, c.month, c.day) IN ((2020, 3, 3, 2020, 3, 3), (2020, 3, 4, 2020, 3, 4))

......

If I were to parameterized it to become an on-demand query, I tried below and got a syntax error:

.....

WHERE (u.year, u.month, u.day, c.year, c.month, c.day) IN (parameter)

......

Is there any workaround on this Any help is greatly appreciated.

[Shashank Gurnalkar]

You need to use your SQL query as shown in an example.

select * from Array1 where A in (p1) and B in (p2)Where A and B are columns from the Array1 table.

Refer to the following Knowledge Base Article and the PDF:

https://support.tibco.com/s/article/How-to-use-Input-Field-Multiple-Line...

https://support.tibco.com/servlet/fileFieldentityId=ka11a0000009jpyAAA&...

You won't be able to pass array inside an array though you can pass an array of parametric values. This is because parameterized custom queries need to be set up using on-demand. And we have designated data type for each parameter which resolves to its corresponding column name. But Spotfire cannot resolve the data type of inner-array to their corresponding columns (A, B).