Editing a custom query with the graphical query builder

i_warning_ex_yellow_sm.jpg

Using the Query Builder circumvents all form-level security and allows users to see all data in all tables in the Object List.

You can edit an existing query using the graphical query builder as a visual aid when creating custom queries. The graphical query builder is designed to create basic queries, more complicated queries may produce an error.

In the Graphical Query Builder, you can select columns from the Objects you want to use, set a sort order, a column alias, and the criteria (where clause). You can also join a column in one object to a column in another object.

There are different types of objects and may start with TBL for tables, VW for views, or SYN for synonyms. It depends on which FAST application you are creating your custom report in, what kind of access you have, and what data your institution uses.

To edit a query using the graphical query builder:

  1. On the Queries menu, click Query Builder.

  2. Select the Query List tab. Existing queries have the binocular icon i_binocular_up.gif on the left.

  1. Click a query. The SQL code is inserted into the large text box.

  2. Click Use Graphical Query Builder.

  1. To add another object to your query, find the object in the list and click it.

The list of columns for the object opens to the right; the letter in the small box on the top left is the alias, if you change the letter, the SQL code is updated.

  1. In the list of columns for an object, you can do the following:

  1. Select a check box for each column you want to add to your query.

  2. Clear a check box to remove a column from your query.

  3. Hover over the column name to view table comments and metadata.

  1. If you are using more than one object, you can join columns of the same type from one object to another object.

  2. In the middle section where the list of columns are, you can do the following:

  3. From the Sort list, you can select ASCending or DESCending.

  4. Add an alias for the column.

  5. Add the Where Clause data in the Criteria and Or... columns.

  1. You can change or add SQL Code to the large text box. Click Refresh to update the data in the list of column above the text box.

If a column in the query is using a function, that column needs to have an alias name. For example, SUM(realamount) AS realamount.

  1. You can click Execute Query to list the data.

  2. To save the query, click Return Query.

  3. Type the Query Name, clear the Private check box if you want everyone to see the query, and then click Save Query.

Related topics

 

 

footer_logo.gif