How to: Use Expressions in Queries

Article
12/03/2014

To use an expression in a query, you can type it directly into the Criteria 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 drag column dividers to resize the columns in the Criteria pane.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Customizing Development Settings in Visual Studio.

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 Criteria pane.

To display an expression in the result set

In the Criteria pane, insert a new grid row.
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.

Sorting Using Expressions

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

Note

Not all databases allow you to sort using an expression. For details, refer to the documentation for your database.

To sort using an expression

In the Criteria pane, insert a new grid row.
In the Column column of the new grid row, type the expression you want to sort by.
If you do not want to display the expression in the query, clear the Filter column of the new row.
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

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

To use an expression as a search value

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

Partager via