Using 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. For details about how to create an expression, see Creating an Expression.

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

In this topic you can read about:

• Displaying Expressions in the Result Set
  
• Sorting Using Expressions
  
  
• Searching Using Expressions

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.

To display an expression in the result set

1. In the Grid pane, insert a new grid row.
   
   
2. In the Column 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. For details, see Creating Column Aliases.

Sorting Using Expressions

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

To sort using an expression

1. In the Grid pane, insert a new grid row.
   
   
2. In the Column column of the new grid row, type the expression you want to sort by.
   
   
3. If you do not want to display the expression in the query, clear the Output column of the new row.
   
   
4. In the Sort Type column, choose Ascending or Descending, and then in the Sort Order column, choose the sort priority for the expression.

Searching Using 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.

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.

To use an expression as a condition

1. In the Grid pane, insert a new grid row.
   
   
2. In the Column column of the new grid row, type the expression you want to use as the condition.
   
   
3. In the Criteria column for the new row, type the value to compare against the condition.

To use an expression as a search value

1. If it is not already in the Grid pane, add the data column or expression you want to search.
   
   
2. In the Criteria column for that data column or expression, enter the expression to use as a search value.

See Also

Creating an Expression | Functions for Expressions | Operators for Expressions | Predefined Variables for Expressions | Query Designer Considerations for SQL Server Databases