Contents

How to design conditions for the SELECT clause

dbForge Query Builder for SQL Server allows you to set up conditions for the queries using the Tabbed Editor. With the Selection tab, you can specify selection criteria for one or multiple columns retrieved from the table(s).

You can access the Tabbed Editor in one of the following ways:

• On the Query menu, select Show Tabbed Editor.
• On the Query toolbar, click Show Tabbed Editor.

Set up conditions for the SELECT clause

On the Selection tab, you can do the following:

• Select the Unique records checkbox to return only distinct values in the column
• Remove columns or change their order by using the corresponding controls on the left of the grid
• Choose a column to retrieve data from
• Specify an alias for the column
• Add scalar functions to the column
• Specify aggregate functions for the column
• Arrange retrieved data for columns in an ascending or descending order
• Specify the WHERE criteria for the retrieved data of columns

Choosing columns for the query

Let’s see how to choose a column for the query using the grid in the Tabbed Editor. When you drag the table onto the diagram, all columns are automatically selected and marked as * on the Selection tab of the Tabbed Editor.

If you want to select specific columns for the query, do the following:

1. On the grid, click the row with *.

2. In the Table & Functions dialog that opens, double-click the column you want to add.

3. Optional: To add the function to the column, double-click the corresponding function from the drop-down list.

4. Optional: To add the operator to the expression, double-click the operator from the dialog.

5. To create a subquery for the column, click Create Subquery in the row or on the Query toolbar. For more information about working with subqueries, see How to work with subqueries.

6. In the Alias column, specify the alias for a table column.

7. In the Table column, select the table from which you want to retrieve the column.

You can specify the alias for the table on the diagram. To do so, double-click the table header in the shape on the diagram, type the alias, and press Enter.

8. In the Aggregate column, select the aggregate function for the column.

The list of aggregate functions supported in the tool:

Aggregate function	Description
avg	Calculates the average amount for the selected values in the column.
binary_checksum	Returns the checksum as a binary value for a row or particular columns of the table.
checksum_agg	Returns the checksum of the values in a table as an integer.
count	Counts the total amount of rows in a particular column.
count_big	Counts the number of items in the SELECT statement as a big integer data type including NULL and duplicate values.
grouping	Calculates the values in the group of the column in the SELECT statement.
grouping_id	Calculates the level of grouping.
max	Returns the highest value in a particular column.
min	Returns the lowest value in a particular column.
stdev	Calculates the standard deviation for all values retrieved in the SELECT statement.
stdevp	Calculates the standard deviation for all values in the expression based on the entire data population.
sum	Returns the sum of all NON-NULL values in a particular column.
var	Returns the variance of all variables in the SELECT statement.
varp	Returns the variance of all variables in the SELECT statement based on the entire data population.

9. In the Order column, specify the order of values arranged in the column.

10. In the Where column, enter the criteria based on which the data in the column will be filtered.

11. When switching to the Text view, you can preview the SELECT statement.

Want to Find out More?

• Previous
• Next