Creating a Query Procedure

• To Create a Query procedure:

• 1. Click New and select Procedure.

     Response: A Procedure Element tab is opened.

  2. In the Data Sources tree, click on the database procedure you wish to use.

  3. Click Select.

     Response: The database procedure is added to the tab. Information Designer analyzes the database procedure and makes a guess as to whether it is a pre-update procedure or a query procedure (it never sets post-procedure by default).

     

  4. Select the Procedure type: Query.

  5. In the Description field, type your own description of the procedure.

  6. If the database procedure requires any Input Parameters, their name and type are displayed.
     

  7. Select whether the input parameter should receive a Default Value by typing a value (of the appropriate type) in the input field. If not, leave the field blank.

     Comment: Type ?param_name to use a parameter as the default value, where param_name is the name to identify the parameter by. Note: If you use a parameter for the default value, you must choose Prompt: None. See Parameterized Information Links for more information.

  8. Select whether you want the end user to be prompted for a single value or not at all, from the Prompt drop-down list.

  9. If you want to allow the input parameter to be Null, select the Permit Null check box.

     Example 1: Type a Default value and set prompt to None. This means that the specified default value will always be used as input parameter.
     Example 2: Leave Default value empty, select Permit Null and Single value prompt. This will cause the end user to be prompted for a value. If the end user does not enter a value in the prompt, Null will be used.
     Example 3: Leave Default value empty, leave Permit Null empty and Single value prompt. This will cause the end user to be prompted for a value. If the end user does not enter a value in the prompt, an error message will appear since Null is not allowed.
     Comment: You cannot select the combination: No Default value, Not allow Permit Null and No Prompt, since this is a paradox.

  10. Expand the Result Columns section.

  11. Either keep the Include all result columns check box selected, or clear the check box and click Add... to specify your own result columns. If the check box is selected, go to step 15, otherwise continue with step 12.

  12. In the Original name field, enter the exact name of a column the database procedure returns. This name is specified in the actual database procedure, so you have to know this beforehand.

  13. Type a Display name to use when the result column is opened in TIBCO Spotfire.

  14. Select the Data type the resulting column should have.

  15. If you want to specify a join between the database procedure and another table, use the Join section.
      Comment: Joining is necessary if you intend to use the result columns from this procedure together with column elements from another data table in an information link. You can only specify one join.

  16. Join columns can be added from three different sources:
      * From the Data Sources tree. This option is similar to selecting join columns at the creation of join elements. Click on a column in the Data Sources tree (or click on the column under its data source in the Elements tree) and then select the From Data Sources Tree option in the Add drop-down list.
      * From a previously specified Result Column. Any result columns that have been defined will be listed directly in the Add drop-down list.
      * From a New Result Column. The third option is used if you want to join over a result column from the procedure but you do not want to use it as output when retrieving data.
      Click Add and select an option from the drop-down list.

  17. Repeat step 16 for another join column. Normally, you will have one column from the data sources tree and one result column in the join.

  18. Select a Condition from the drop-down list.
      Comment: There are two basic types of joins that can be done between tables: inner joins and outer joins. An inner join will return rows for which only the matching fields in both tables are equal. An outer join will return all the rows (including NULL values) from one table, and only the matching rows from the other table.  See also Freehand Joins.

  19. Click Save.

      Response: The Save As dialog is displayed.

  20. Click to select the folder where you wish to save the procedure element.

  21. Click Save.

      Response: The procedure is saved in the selected folder, and will be denoted with the icon. Note that the procedure object will be visible to the end user in the list of information links. You can also make larger, more complex information links using the procedure when creating an information link in Information Designer.

See also:

Procedures Overview.