Assigning values through SQL statements

In addition to setting field values based on BMC Remedy AR System data, you can retrieve data from other sources by submitting SQL queries for use within BMC Remedy AR System workflow. Much like issuing a search to get data from another form, you can issue an SQL statement to retrieve data from any relational database table. This feature enables you to select data from databases other than BMC Remedy AR System server databases, which is useful for integrating BMC Remedy AR System with external data sources.

The ability to submit SQL statements enables you to issue complex queries to the database. This is useful if you want to use database features specific to a particular database platform.

The SQL statement you use to set a field value in the Set Fields action has a different result than the Direct SQL action (see Direct SQL action). In the Set Fields action, you use SQL to search the database for information and then use the returned values to set fields. With the Direct SQL action, the SQL command is not expected to return a value.

To run more than one SQL command, use stored procedures or functions or any other extension supported by your database. A stored procedure with a Set Fields action executes all its commands but does not return a value.

For the most effective use of SQL statements, you must have a general understanding of relational databases and a specific understanding of the relational database underlying your BMC Remedy AR System.

 To assign a value by submitting an SQL command

1. From the Server Name list, make one of the following selections:
   • For active links, select the server that contains the value that you want to retrieve.
   • For filters and escalations, select the current server.
2. From the Data Source list, select SQL.

3. In the SQL Query field, enter the SQL command to issue to the database.
    For example, you can enter an SQL statement with a command to display three columns of data from a table and sort the data in ascending order based on the first column:
   SELECT BUG_ID, FIRST_NAME, TECHNCN FROM CUSTMR_INFO ORDER BY 1 ASC
   
   SQL statement with SELECT command in SQL Query field
    (Click the image to expand it.)
   
   You can enter a command by typing it, or by entering a qualification in the Expression editor. When you type a command, you can use Ctrl+S to display a completion menu for SQL syntax, and $ to display a completion menu for fields and keywords.
    If you are not sure which fields to reference, you can build an SQL statement in the Expression Editor. Entering qualifications in the Expression Editor. 

   Note

   Enter only one SQL command for each Set Fields action. Do not end the SQL command with run or go.

   Use the SQL Query field menu button to insert field values or keywords in the SQL statement using the Expression Editor. As shown in the following example, fields and keywords must be enclosed in dollar signs to indicate that the server should expand these values before issuing the command: 

   
   SELECT BUG_ID, FIRST_NAME, TECHNCN FROM CUSTMR_INFO WHERE
   <colName> = '$<field>$'

    You might have to insert single quotation marks manually around the parameter, depending on the content of the expanded value and the context in which you are using it. For example:

   • If colName is a character field in the CUSTMR_INFO table, you must add single quotation marks around $ field$ so that the database interprets the expanded field value as a character string.
   • If colName is a numeric field, using the single quotation marks results in an SQL syntax error.

   • You also get an SQL syntax error if you omit the quotation marks but field contains character data. 

     Note

     BMC Remedy AR System does not verify the validity of your SQL command. The syntax you use must be recognized by the underlying SQL database on which BMC Remedy AR System is running.

4. From the If No Requests Match list, select a handling option to control how the system responds when the SQL command returns no matches.
    For more information about the options in this step, see Set Fields action.
5. From the If Multiple Requests Match list, select a handling option to control how the system responds when the SQL command returns multiple matches.
    For more information about the options in this step, see step 7 in To use the SERVER data source.
6. From the Name list, select the field that you are setting with this action.
   
   Selecting field being set
    (Click the image to expand it.)
   
7. From the Value list, select SQL Result Column, and then select $ n$.
    The $ n$ variable represents the number of a column in the SQL result table constructed from the results of the SQL command. When the active link, filter, or escalation executes:
   1. The SQL command is issued to the database.
   2. The results of the SQL command are used to construct an SQL result table.
   3. The value from column 1 of the SQL result table is loaded into the field that contains a $1$, the value from column 2 of the SQL result table is loaded into the field that contains a $2$, and so on.
       If an SQL command includes three columns, use a $ n$ variable as high as 3. If you specify a $ n$ variable that is greater than the number of columns in the SQL command, a NULL value is returned. If you use an asterisk in an SQL command, for example SELECT * FROM CUSTMR_INFO, the menu lets you select an $ n$ variable as high as 10. However, if you know that 15 values are returned, entering $14$ works. Because the first column in the form table is used to set the field that contains $1$ (and so on), you must know the order of the form columns to load the correct data into the correct field.
       The following figure shows an example of values assigned to fields. If you enter the same $ n$ variables (that is, $1$ in the Long Description field, $2$ in the Short Description field, and $3$ in the Work Around field) and the SQL command shown in the following figure, the returned results create an SQL result table that looks like the following figure.
      
      Results returned from SQL command
      
      Because this action also specifies that multiple matches should display a selection list, a selection list of available SQL result table entries appears when the active link executes.
       If you select the second selection list entry, the contents of BUG_ID are loaded into the Long Description field ($1$ ), the contents of FIRST_NAME are loaded into the Short Description field ($2$ ), and so on, as shown in the following figure.
      
      How database columns are inserted into fields
      
      
      Entering a $4$ variable value without an actual fourth column in the SQL command inserts a NULL value into the field.