Details on Custom Query

This dialog allows you to create your own custom database query. The query results in a custom table which in turn can be used to set up a view in the selected connection in the same way as other database tables. Queries are written in a language that is supported by the target database. For example, for a Microsoft SQL Server database, you would write your custom query in SQL.

Option	Description
Query name	The name of the custom query which will also be used as the table name for the resulting table.
Query	Here you can type or paste a query in a language that the database understands. The query can be parameterized and the used parameters are specified on the Parameters tab.
Verify	Verifies the query and retrieves the result columns which are automatically added to the Result Columns tab. If an error is detected you will get some information about what is wrong under the Query field. Note that the data type entered for a result column must match the actual data type returned from the external system or else the custom query will not work. The verification checks that the metadata returned matches the expected metadata entered in the dialog only. This means that if you make changes in the dialog after a first verification and do not update the result columns, then verification will not find any errors.
Preview	[The preview is supported for connections to external data sources which support the TOP/LIMIT clause only. This is required in order to limit the amount of data returned in the preview.] Allows you to see a preview of the first 100 rows from the resulting table, together with any data table or column properties from the database.

Result Columns Tab

Result columns used in the specified query are automatically added to the Result Columns tab when the query is verified, but you can also manually add or remove result columns.

Option	Description
Result columns	Lists all columns that will be included in the resulting database table. Note that only those columns that are included in this list will be shown in the resulting view, even if the specified query selects more columns.
New...	Opens the New Result Column dialog where you can specify the name and data type of a new result column.
Edit...	Opens the Edit Result Column dialog where you can change the name and data type of a result column.
Delete	Deletes the selected result columns from the list.
Move Up	Moves the selected result columns up in the list and, hence, forwards in the order the columns are presented in Spotfire.
Move Down	Moves the selected result columns up in the list and, hence, backwards in the order the columns are presented in Spotfire.

Parameters Tab

Parameterized queries can be used to retrieve different data based on the current user, domain or group. You can also use parameters to control on-demand loading of the data table. When the parameters are used in a query they are prefixed by a question mark.

Option	Description
Parameters	Lists all parameters that can be used in the query.
New...	Opens the New Parameter dialog where you can specify the name, data type and value type of a new parameter.
Edit...	Opens the Edit Parameter dialog where you can change the name, data type and value type of a parameter.
Delete	Deletes the selected parameters from the list.
Use parameters for personalized queries (users and groups)	Select this check box to enable the use of personalized queries based on the predefined parameters current_user, current_groups or current_user_domain. By including these parameters in the query you can set up a data connection to return only information applicable for a certain user, group or domain.