What does this set of sql statements do?

You can also use vendor forms to access external data, which could reduce the risk of exposing your application to any issues with the other source. For information about accessing external data using vendor forms, see Using buttons and menu bar items to execute active links.

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 Defining Direct SQL active link, filter, or escalation actions). 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 AR System .

Warning

Because AR System passes SQL commands to the database without checking the syntax, all commands are submitted to the database. Make sure all submitted commands achieve the needed result. Your SQL commands should comply with ANSI SQL standards, so that single quotes are reserved for strings, and double quotes are reserved for use with database object names only.

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
   
   

   
   
   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.

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

   SELECT BUG_ID, FIRST_NAME, TECHNCN FROM CUSTMR_INFO WHERE
    = '$$'

   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:

   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.

     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 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 and structures.
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 "To use the SERVER data source" in Creating a Set Fields action.
6. From the Name list, select the field that you are setting with this action.
   
   
   
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.

Performing the SQL operation

Observe the following general rules for using SQL commands:

• You need not use every value that is returned from the SQL command. If you do not use any values from the SQL command, the Set Fields action acts like the default selection CURRENT SCREEN (active links) or CURRENT TRANSACTION (filters or escalation), and the system removes your SQL query.
• You can use the same value in more than one field.
• You can issue only one SQL command per action. You cannot enter two commands separated by a semicolon and have both commands run. To run a set of commands, create separate actions, or create a stored procedure and run that. However, stored procedures do not return values.
• Turn on AR System server  SQL logging to debug the SQL syntax if it returns unexpected values or results. A good debugging strategy is to start an SQL interpreter (for example, SQL*Plus for Oracle, Command Center for Microsoft ISQL/w for Microsoft SQL Server) and to enter the same SQL command directly into the database to verify its validity.
• Because there is no error checking on the SQL statement, run the SQL statement directly against the database (as a test) before you enter it into the SQL Command field. You can then copy and paste the tested SQL command directly into the SQL Command field.
• If the SQL operation fails, an  AR System  error message and the underlying database error message appear.
• You can affect database performance by how an SQL query is written. If the row has many columns, a *SELECT ** SQL command can have a greater performance impact than if you select specific columns. For more information, see your relational database documentation.

To use SQL commands, familiarize yourself with the features of your underlying database. For Oracle, Microsoft SQL Server databases, all SQL commands are issued by the ARAdmin user (or the AR System Database User defined during installation).

Depending on which database you are using, the data must be accessible to the user issuing the command. If you are running AR System  as one of these users without permission to access the database, you cannot issue the SQL command. 

What are the 5 SQL statement types?

What are the 4 SQL commands?

What are the 3 types of SQL commands?

What are the 3 main clauses of a SQL statement?