Use Expressions in a Query

To use an expression in a query, you can type it directly into the Grid pane or you can enter it in the SQL pane as part of the statement. Entering expressions is similar to entering column names.

Tip

To make it easier to see long expressions on your screen, you can resize the columns in the Grid pane.

Displaying expressions in the result set

You can display an expression in the result set by specifying the expression in place of a column in the Grid pane.

  1. In the Database window, click Queries   under Objects, click the query you want to open, and then click Design on the database window toolbar.
  2. In the Grid pane, insert a new grid row.
  3. In the column of the new grid row, type the expression whose results you want to display.

When you display the results of an expression in the result set, the database assigns a column heading to it using the format "Exprn," where n is the number of the expression in the current query. You can replace this with a more meaningful alias for the expression.

Sorting with expressions

You can sort by the results of an expression. As with columns, you specify the sort type and sort order.

  1. In the Database window, click Queries   under Objects, click the query you want to open, and then click Design on the database window toolbar.
  2. In the Grid pane, insert a new grid row.
  3. In the column of the new grid row, type the expression you want to sort by.
  4. If you do not want to display the expression in the query, clear the Output column of the new row.
  5. In the Sort Type column, choose Ascending or Descending, and then in the Sort Order column, choose the sort priority for the expression.

Searching with expressions

There are two ways to use an expression for searching. The expression can be the condition against which you compare values, or it can function as the value you are comparing.

Expression examples

The following example illustrates how you can use an expression as the condition in a WHERE clause:

SELECT ord_num, ord_date 
FROM sales
WHERE (price * .9) > 20

In contrast, the following example illustrates the opposite use of an expression, in which the expression is the value you are comparing:

SELECT ord_num, ord_date
FROM sales
WHERE (ord_date >= DATEADD(day, -10, GETDATE() ) )

The way you specify an expression for searching depends on whether it appears as a condition or as a value to search.

Use an expression as a condition

  1. In the Database window, click Queries   under Objects, click the query you want to open, and then click Design on the database window toolbar.
  2. In the Grid pane, insert a new grid row.
  3. In the Column column of the new grid row, type the expression you want to use as the condition.
  4. In the Criteria column for the new row, type the value to compare against the condition.

Use an expression as a search value

  1. In the Database window, click Queries   under Objects, click the query you want to open, and then click Design on the database window toolbar.
  2. If it is not already in the Grid pane, add the data column or expression you want to search.
  3. In the Criteria column for that data column or expression, enter the expression to use as a search value.