• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 18. Importing and Connecting > Using Calculations to Build Complex Quer...

Using Calculations to Build Complex Queries

Saving a query in a field saves lots of time and agony, especially if you aren't accustomed to using SQL on a regular basis. If you squirrel away your most useful queries, you don't have to relearn SQL or rethink your needs every few weeks. Unfortunately, you can't run multiple SQL queries in a single script, or refine one query with additional criteria. If you want to make your SQL queries more flexible, your best tool is a calculation field. By breaking your queries into parts and recombining them with a calculation, you can quickly build more useful commands.

To create an SQL command by calculation:

Choose File > Define Fields (Ctrl+Shift+D/ Command-Shift-D).

When the Define Fields dialog box appears, type gCriteria as the field name, make it a global field, and click Create.

When the Options dialog box appears, choose Text as the data type, then click OK.

Repeat steps 2 and 3 for as many modular ODBC commands as you would like to store.

Choose File > Import Records. When the Open File dialog box appears, choose ODBC Data Sources from the Files of type drop-down list.

When the Select ODBC Data Source dialog box appears, choose your ODBC file from the list. Enter your user name and password, or just click OK if you don't need one.

When the SQL Query builder appears, click to select the database table you want in the Tables listing on the left, then select the ODBC data fields you want from the Columns section on the right.

Click to select the Where tab (Figure 18.52).

Figure 18.52. Select the Where tab to choose exactly the group of records you need.

In SQL you use Where to narrow the selection according to the options you choose.

Click the Show only selected columns check box to prevent choosing a criteria that isn't part of the columns you've already selected (Figure 18.53).

Figure 18.53. When you check Show only selected columns, only those columns you chose to include in your query will be available in the column drop-down list.

From the column drop-down list on the right, select the ODBC data column you want to use to define your criteria.

For this example, we choose Salary.

In the Operator drop-down list, select the operator you need to define your criteria.

In our example, we choose the Equal or Less Than operator (<=) because we are searching for people with salaries equal to or below a certain amount (Figure 18.54).

Figure 18.54. Choose an operator that matches the criteria you've selected.

Choose the Column or Value radio button to finish your query. If you want to compare two different data columns, select Column. If you want to define a specific number, select Value. The dialog box will update depending on which choice you make.

In our example, we select Value (Figure 18.55).

Figure 18.55. When we choose Value, the dialog box updates to show one input box instead of two dropdown lists because we will not be comparing two columns as part of our query.

Type the value you want in the input box to the right of Value, or click Values to open the Column Values dialog box, where you choose a value from those in the ODBC data (Figure 18.56). Click OK to close the Column Values dialog box.

Figure 18.56. The Column Values dialog box displays only values that appear in the database.

Click Insert into SQL Query to transfer your choices into the SQL Query formula box (Figure 18.57).

Figure 18.57. The Where statement isn't added automatically to your SQL Query—you must click the Insert button.

Select everything in the SQL Query box and copy it to the clipboard. Click Cancel to close the SQL Query builder box.

On your layout, select one of the global fields you created and paste the SQL query into the field.

Cut and paste your Where section of the query into another global field.

By dividing the query into two pieces, you make each of them modular. Without having to return to the Query Builder, you can vary the fields you import separately from the salary criteria you specify.

Choose File > Define Fields (Ctrl+Shift+D/ Command-Shift-D). When the Define Fields dialog box appears, type a new field name, make it a calculation field, and click Create.

We call our calculation field “workgroup.” We'll use this field to combine query modules for the import script.

When the Specify Calculation dialog box appears, select the global field that holds the first part of the SQL query from the field list on the left (Figure 18.58).

Figure 18.58. Always begin with the global field that contains the Select portion of the query.

When you combine SQL commands, the Select portion must always come before the Where portion.

Click to select the ampersand (&) operator button, then select the second global field where you placed the other half of the query. (Figure 18.59).

Figure 18.59. The finished calculation combines two global fields to make a complete query that selects ODBC columns based on your Where criteria and imports them into your database.

Make sure that Calculation result is set to Text, then click, then Done.

Delete the global fields from your layout so their text can't be changed by mistake.

You can repeat this process to create several different combinations of selections and criteria, combining them for your most frequent SQL queries.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint