Create an SQL Query

These steps provide a basic overview of query construction, but do not go into detail.

To create a SQL type query:
  1. Enter a name for the query in the Query Name field.
  2. Enter a title for the query in the Title field.
  3. Enter a description for the query in the Description field.
  4. Select SQL in the Type field.
  5. To make the query available to be assigned to a Work Center, select the Assignable in Work Center checkbox.
  6. Enter the query in the Query Text field, including the Select, From, and Where clauses.
    Note: The Select clause identifies the information that you want to return from the database. For example, you might enter Select Name, Address, City, Zip to query for a name, address, city, and zip code. The Where clause filters the information returned by the query. When creating a Where clause, specify the primary table that information is being requested from. If you are using multiple tables in the query, you must define the “joins.” Joins identify how the tables are linked; that is, the unique set of values that exist in both tables. After identifying the tables, specify how the system filters the data.
    Note: Using the address example from above, you may want to pull name and address information from the HQ Company Setup (HQCO) table. Additionally, you may want to filter for a specific company. To do this, the WHERE clause would look like this: FROM HQCO WHERE HQCO=1. This clause has the query pull records from HQCO for Company 1. No joins exist in this example.
  7. Click the Update Columns button. All specified database columns now display on the Columns tab.
    Note: Using the example from step 5, the following columns would display on the Columns tab: Name, Address, City, and Zip.
  8. Use the Columns tab to define how the columns should display in the Work Center. Press F1 in any of the fields on this tab for more information.
  9. Add parameters to restrict the records that the query returns. See Restricting Queries with Parameters.
  10. Associate the query with specific Work Center templates. See About Associating Queries with Work Center Templates.
  11. Save the query.
  12. If you want to link the queries so that users can drill-through them on a Work Center, create an inquiry using the Links tab. See Creating Inquiries.
  13. Set security for the query using the About the VA Inquiry Security Form form.