Creating SQL menus

An SQL menu pulls values from a database table by using an SQL query. The database values make up the SQL menu items. By using an SQL query to create a menu, your application can do the following:

• Select data from databases other than BMC Remedy AR System server databases.
• Issue complex queries to the database. This is useful for customers who want to use database features specific to a particular database platform.

To use SQL queries effectively, you must understand relational databases in general and your relational database in particular. If an SQL query is database-specific instead of generic, moving the SQL menu definition to another environment might be difficult.

Before creating an SQL menu, determine what information you want to search for in the database and what information your SQL query should return. For example, see Sample-SQL-menu.

To create an SQL menu

1. In BMC Remedy Developer Studio, select File > New > SQL Menu.
2. Select the server on which to create the menu, and click Finish.
   A new SQL menu appears as shown in the following figure.
   
   New SQL menu
   (Click the image to expand it.)
   
   
3. In the Refresh list, select the appropriate refresh mode.
   See Refreshing-menus.
4. In the Server list, select the database server to which the SQL query will be issued.
5. In the Label Index List field, enter the numerical index of the database column that contains the information to display as menu item labels.
   To create a hierarchical menu, enter up to five index numbers separated by commas. The first number becomes the first level, the second number the second level, and so on.
   Only the first 80 characters of each field value are displayed.
   In multilevel menus, items in all levels (except the last level) that do not have a value appear blank. If the last-level menu item does not have a value, it does not appear.
   In single-level menus, if the first or last item does not have a value, it does not appear. If items in the middle of the menu do not have values, they appear blank.
6. In the Value Index field, enter the numerical index of the database column that contains the information to load into the field when users select a menu item.
   Only the first 255 bytes of each field value are displayed.
7. Click the ellipsis button next to the SQL Query field, and use the Expression Editor to build the SQL query to issue to the database to create the menu.
   For more information, see Using-the-expression-editor-and-content-assist.
   Consider these tips when building the query:
   • You can enter only one query for each menu. You cannot enter two queries separated by a semicolon and have both queries run. To run a set of queries, you must create a stored procedure or function and run that.
   • Because BMC Remedy AR System does not verify the validity of your query, run the query directly against the database (as a test) before you enter it into the SQL Query field, and then copy and paste the tested query into the SQL Query field.
   • If the query returns unexpected values or results, turn on SQL logging in the database to debug the SQL syntax. An additional debugging strategy is to start an SQL interpreter, for example, SQL*Plus for Oracle, Query Analyzer or Microsoft ISQL/w for Microsoft SQL Server, and enter the same SQL query directly into the database to see what results are returned.
   • If the SQL operation fails, a BMC Remedy AR System error message and the underlying database error message appear.

   • You can greatly affect database performance by the way that you write an SQL query. If a 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.
     See also Database-security-issues.

     Note

     If the query includes a field value from the specified form in its WHERE clause, you cannot use the character field pattern $MENU$ for any field to which the menu is attached. Because the server cannot resolve the field references, that value is always rejected.

8. (Optional) Modify the menu's change history.
   See Recording-menu-change-history.
9. (Optional) Add Help text to the menu.
   See Creating-help-text-for-menus.
10. Select File > Save.
11. In the Save Menu As dialog box, enter a name for the menu, and click OK.
    Menu names must be unique on each BMC Remedy AR System server.
    Names can have as many as 80 characters, including spaces. Names can include double-byte characters, but avoid using numbers at the beginning of the name.
12. Attach the menu to any character field in any form.
    See Creating-data-fields and Menu Name.

See also: