Advanced SQL Designer in GridWorX

GridWorX provides a SQL Command Designer to help you create an advanced query for your selected database.

To run an advanced query on an open database:

Start the database connection as described in Connecting to Open Databases.
Click the database connection and select New Data Source.

New Data Source Selection

Click the Edit Command Text icon and select Advanced SQL Designer.

Advanced SQL Designer Selection

After the database schema loads (this may take several minutes depending on the size and location of the selected database), the SQL Command Designer opens.
Select the data tables to include in the database query by putting a checkmark beside each one to be included. When you are done, click OK.

SQL Command Designer with MultiSelect Tables Dialog Box

The Tables and Columns tab appears. The wizard displays all columns from all the tables you selected. For each table you chose, select which data columns to include in the database query by checking the box next to the name of each column, as shown in the figure below. For this example database query, the Orders and Customers tables are selected.
If you select multiple tables, you can join the tables together by simply dragging a data column from one table over to the corresponding field in another table. An arrow appears, indicating that a join between the two tables exists, as shown in the figure below. In this example, the CustomerID column in the Customers table is linked to the CustomerID column in the Orders table.

SQL Command Designer with Tables and Columns Tab

To open the Table Join Properties dialog box, right-click the join arrow and select Edit Join. This opens the Table Join Properties dialog box, which lists the names of the tables and columns that are joined. Use the expression options under SQL Join Type to define the relationship between the two columns (in terms of Equal to, Greater than, Less than, etc.).

Note: Refer to your SQL language documentation for details about SQL Joins.

Table Join Properties Dialog Box

On the Table Join Properties dialog box, you can also manually write a condition for the custom join by selecting the Custom Join Condition option and then clicking the Edit button to the right of the option. This opens the Custom SQL Join Condition dialog box, shown below. You can click the Columns button to choose column names from the database, the Operators button to select an operation (such as addition, subtraction, multiplication, etc.), and the Functions button to add a function (mathematical, string, date/time, etc.).
Click OK to close the Custom SQL Join Condition dialog box. Click OK to close the Table Join Properties dialog box.

Custom SQL Join Condition Dialog Box

This returns you to the Tables and Columns tab of the SQL Command Designer. To change the order in which the columns will be displayed, select a column and click the up or down arrow button to move the column. To insert more columns, put a checkmark beside the columns in the tables in the main display area.
To add or change a column expression or add a column to write an expression for, click the Add Column Expression button, which is below the up and down arrows. This opens the SQL Expression dialog where you can edit the Expression Title and SQL Column Expression fields. When you are finished, click OK.

SQL Expression Dialog Box

Go to the Records Grouping tab, which is shown in the figure below. This tab lists the selected table columns and expressions. Grouping aggregates records in a data column that have the same value. Click in the Group By field to change the grouping options for the data tables (or you can leave them unchanged). Grouping and aggregation are performed using standard SQL language grouping; refer to the SQL language documentation for details. For a date/time column, if you would rather run a period summary, the next step will tell you how.

As appropriate, assign an Aggregate Function (such as a Count, Average, Maximum, Minimum, Sum, Standard Deviation, etc.) to each of the columns you did not include in the grouping list. To set an Aggregate Function, click on the column or expression name; this enables the Aggregate Function’s drop-down list for the column or expression. The records of columns that are assigned the same aggregate function will be grouped together in the query result.

Records Grouping Tab

Go to the Records Filtering/Sorting tab to enter any filters you would like to apply to the data, as shown in the figure below. To add a filter, click the button that has the large green plus sign.

Records Filtering/Sorting Tab

The Filtering Criteria dialog box, shown in the figure below, helps you write proper syntax for SQL filtering expressions. In the Filter Type, you can select Relational Filter or Expression Filter. If you choose Expression Filter, the dialog box looks like the example shown below. Here you can write a SQL expression for the filter; the Columns button allows you to insert column names from your selected database table.

Note: Refer to your SQL language documentation for details about SQL expressions.

Expression Filter Dialog Box

If you choose Relational Filter as your filter type, complete the following fields:

1. - Column Name to Filter On - the drop-down list contains the columns you can filter on. Choose one.
  - Relation Type - select an SQL function (Equal To, Not Equal To, Greater Than, etc.)
  - Relates To - select the item for comparison. You can choose one of the following:
  - Constant Value - Click the button with the database icon on it to select a value from the database.

Relational Filter with Constant Value selected

1. 1. - Parameter - Click the button with the @ icon on it to select a parameter from the Parameter browser. The parameter appears in the Query Parameter field, as shown here.

Relational Filter with Parameter selected

You can also choose a data table value directly from the Relates to field as shown in the following figure.

Relational Filter with Parameter selected

You can create additional filters by repeating the prior two steps.
Once you have created all of your filters, you can drag and drop them from one tree node to another, or if you create a new filter while some other filter is selected on the tree, the newly created one will be shown as a child tree node. The tree-like structure for filters displays how they are combined, using AND/OR Boolean operators and parentheses. The rule is simple; each filter node is first combined with its “children,” and then the resulting expression is combined with the “siblings.” Try dragging and dropping several filters onto one another and watch how the SQL preview changes to get an idea of how this works. Click the Toggle Bool button to switch between the AND/OR boolean operators.

Records Filtering/Sorting Tab

Use the bottom half of the Records Filtering / Sorting tab, shown above, to sort the database records. Now that you have defined parameters for filtering or restricting data from columns you can specify the priority by which the columns and the records in each column are sorted. You can choose to sort on any of the selected columns, as shown in the figure above, or leave them unsorted.

To determine the order in which the data will be displayed within in each column, select that column from the drop-down list and select one of the following sorting options:

1. - Ascending: Sorts the data in the column in ascending order (i.e. from least to greatest).
  - Descending: Sorts the data in the column in descending order (i.e. from greatest to least).

Click the OK button to complete the query build. If the schema is unknown at this point, a message appears. Click OK. The Parameter Values dialog box appears, and looks something like the following example.
The GridWorX64 Server Data Source dialog box appears with the SELECT command you just created.

GridWorX64 Server Data Source Name with Select Command

When you click the Test icon, the Parameter Values dialog box appears. Type valid values for the parameters that are highlighted in gray to be used for a test query, then click OK.

Parameter Values Dialog Box with Requested Values

The Query Results dialog box appears and lists valid records retrieved using the parameter values you provided for the query. You may need to test of change your query if you do not see the results you expected. Click Close to close this dialog box.

Query Results