Using an AI-SQL function in a Custom Query


Even though AI-SQL functions are not supported in general Spotfire interfaces and expressions, they can be used as a part of a custom query when creating the connection to Attivio.

Note: As the number of in-database visualizations, filters, etc., increases, the live queries generated get more complex. Sometimes, the Attivio database engine will not be able to process these queries, which may prevent  visualizations and filters from being rendered. If you experience this issue, try using a mix of in-database data tables and in-memory data tables, or use in-memory data tables only.

  1. Follow steps 1 to 5 in Loading Data from explore.attivio.com to connect to the Attivio data source.

  2. In the Views in Connection dialog, click Custom Query and select New Custom Query....

  3. In the Custom Query dialog, type a Query name.

  4. Type or paste the custom query containing the desired AI-SQL function in the Query field.

  5. For example:

    select order_id, ship_mode, quantity

    from Orders

    where ship_mode = STARTSWITH(?ship_mode)

  6. If the query contains any parameters, click on the Parameters tab and click New... to define the parameters used in the query.

  7. Comment: The example query above contains the parameter ship_mode, which should be defined as a single value string.

  8. Click Verify to verify the query and to populate the Result columns tab.

  9. Click OK twice.

  10. If the query contains a parameter, in the Add Data Tables dialog, make sure Load on demand is selected and click Settings.... Otherwise, go to step 10.

  11. In the On-Demand Settings dialog, select the parameter (e.g., ship_mode) and click Define Input....

  12. Comment: For example, you may want the parameter to be controlled by a property control in a text area, or you can define a fixed parameter value directly in the dialog.

  13. When all required parameters have been defined, click OK in all dialogs to load the data.

See also:

Attivio (Teiid) Data Types

Details on Attivio Connection

Using Properties in the Analysis