ApexSQL Knowledgebase: Visual Query Editor

LINKS

General

Apex SQL Audit
Apex SQL Clean
Apex SQL Code
Apex SQL Diff
Apex SQL Doc
Apex SQL Edit
Apex SQL Enforce
Apex SQL Log
Apex SQL Script

Knowledgebase Home

SITE FEED

Site Feed

Support Forum

The fastest and most effective to get answers to your questions quickly. Click Here for more info.

FAQs

Quick answers to common questions. Click Here for more info.

Troubleshooting

Known issues and basic troubleshooting techniques for problems or unexpected behaviors. Click Here for more info.

Knowledgebase

Technical tips, How-to articles, and other tutorials about ApexSQL Tools. Click Here for more info.

Online Documentation

All of our help files for download or convenient viewing online. Click Here for more info.

Videos

Watch product demos, training videos, or tutorials of our products' main features. We frequently upload new videos so check often for updates. Click Here for more info.

ApexSQL Knowledgebase

Tips and How-to Articles for Apex SQL Tools

Visual Query Editor

This KB discusses how ApexSQL Edit's Visual Query Editor can speed up your T-SQL coding.

DESCRIPTION
In this article, we discuss the Visual Query Editor (VQE) of ApexSQL Edit and how you can get the most out of it.

Activating the Visual Query Editor
By default, the VQE is not activated automatically in ApexSQL Edit. To activate it, right-click on the menu/toolbar area and select "Customize":

Go to the "Commands" tab and select the "Query" item from the Categories list. Scroll down the list of "Commands" and at the bottom you can find the "Visual Query" item:

Drag and drop this item to the menu/toolbar area. Important: This is a one-time setup. Once set, the VQE can be accessed from the Main Menu or by hitting F11:

The Visual Query Editor

The Visual Query Editor can be divided into the following subparts:

1. The Query Building Area: This is the main area where the visual representation of the query is displayed. This area allows you to add database objects, define links among them, and configure properties of tables and links.

2. The Database Tree Pane: In this area, you may select the objects such as tables, views, or procedures that you want to build a query from. To build a query, simply drag and drop them on the Query Building Area.

3. The Columns Pane: This area is used to perform operations on your query output columns and expressions. You can define field aliases, sorting, grouping, and criteria.

4. The SQL Output Area: This is where the textual representation of the query will be displayed.

Note that the VQE is a two-way tool. This means that the modifications done in the Query Building Area will always be reflected in the SQL Code Area and vice versa.

Getting started
To illustrate ApexSQL Edit's VQE, we use the Northwind Database. The first step in using the VQE is selecting the database object that your query will be based on. A database object is selected when, and only when, it is displayed in the Query Building Area. Again, this means that objects only exist in the VQE when they are shown in the Query Building Area. Suppose you have a query that's based on the Orders table. You have two ways to bring up the Orders table in the Query Building Area. The first way is to select the Orders item in the Database Tree Pane, drag it from there, and drop it to the Query Building Area. After dropping the Orders item, it will be displayed in the Query Building Area:

The second way of adding objects to the Query Building Area is to use its context menu:

By selecting the "Add Object" menu item, you open the "Add new object" dialog:

This dialog contains three tabs--one for each object type that can be added to the VQE. You can browse this dialog just like any other list. If a database contains many objects in different schemas, the "Filter objects by Schema name" dropdown box can be used to limit the number of items displayed in the list. Objects can be filtered by the schema to which they belong:

As displayed above, there is no user-defined schema in this version of the Northwind database. By selecting "(All objects)," the filter can be removed and all objects of the database will be displayed again. Once the object to be added to the Query Building Area is found, the corresponding item can be selected in the list. Then, click on the "Add Object" button,

.

The Query Building Area now looks more or less like below:

To control the visibility of a column in the final SELECT list, simply check or uncheck it. In above example, CustomerID, OrderDate, and ShippedDate columns are selected and will thus be included in the SELECT list. By marking a column checked, it is instantly added to the Columns Pane and the SQL Output Area. Notice that the Order table item's caption in the in the Query Building Area shows (3)dbo.Orders. ApexSQL Edit automatically displays the number of checked columns.

Using more than one table
The VQE has the ability to identify the relationships among tables added to the Query Building Area. The result of dragging and dropping the [Order Details] table to the Query Building Area looks like this:

The link between both table objects are shown above. By reading SQL Server's metadata, ApexSQL Edit determines that both table are in a 1:m (1 to many) relationship.

Editing a selected database object
Each SQL Server source code parser must be able to uniquely identify columns and tables in a query. Typically this is achieved by using the three part naming convention-- schema.table.column. Using this convention, it is immediately obvious which column belongs to which table. The syntax however adds complexity to the query definion and makes code hard to maintain. To simplify this, you may use table aliases for each table participating in the query.

Consider the query below:

SELECT DBO.[ORDER DETAILS].ORDERID, DBO.[ORDER DETAILS].PRODUCTID
FROM DBO.[ORDER DETAILS]

With a table alias, the query will look like this:

SELECT OD.ORDERID, OD.PRODUCTID
FROM DBO.[ORDER DETAILS] OD

Although both queries are absolutely identical with respect to their execution plan and resultset, the latter is more readable. The beauty of using table aliases is made more obvious when using 4 or more JOINs. VQE supports table aliases. An alias for an object can be created by right-clicking on the object in the Query Building Area:

From the context menu, choose "Edit...". This opens the "Datasource Properties" dialog:

Enter the desired alias and click OK. The query immediately reflects the change introduced by the alias:

Editing relationship properties
This INNER JOIN is the default relationship used by the VQE. This can be changed by right-clicking the link between two tables:

Then, choose "Edit..." from the context menu to add the "Link Properties" dialog:

Above image shows the unmodified link property. The dialog is divided into a left and a right part with a linking operator between them. This is a visual presentation of the underlying SQL source code. Right below the object names, there are two checkboxes. With INNER JOINs, none of these checkboxes will be marked. Below the checkboxes is the column that joins the two tables. To change the INNER JOIN to an OUTER JOIN, mark that checkbox below the left table:

You now have a LEFT JOIN from Orders on [Order Details]. In order to change this condition to another, select the appropriate operator from the dropdown box:

Most of the common operators can be selected from the list.

The Columns Pane
The Columns Pane is the area in the VQE that's located directly below the Query Building Area:

This grid contains all columns currently selected for output in the SELECT list or the columns used for sorting, grouping, or filtering. Each column has a corresponding checkbox, the check status of which determines its inclusion to the resultset. A column alias can also be specified for each column. You may--

-specify if the column should be included in an ORDER BY clausem

-specify the ordinal position of the column in the ORDER BY clause,

-or specify if an aggregate function should be applied to the column and whether the column should appear in the WHERE, GROUP BY, HAVING clause of the query:

If a column is included in a WHERE clause, you may enter the condition into the "Criteria" field at the right of the "Group By" column:

Editing Query Properties
Next we discuss the Q in the Query Building Area:

The "Query Properties" dialog can be reached via this symbol:

In this dialog, you can specify if the TOP clause should be applied to the query, and whether a number of rows or a percentage of rows (with ties or not) should be returned. There are several other different aggregating functions available:

Derived Tables
The VQE in ApexSQL Edit fully supports derived tables. A derived table a virtual table that is derived from other base tables or views. Consider this:

SELECT ORDERID
FROM ORDERS

The above query uses the base table Orders in the FROM clause.

SELECT ORDERID
FROM
(SELECT ORDERID
FROM ORDERS) AS T1(ORDERID)

The second query uses a derived table in its FROM clause. Derived tables can be used where table expressions are allowed. To add a derived table to the Query Building Area, use its context menu:

From the context menu, choose "Add Derived Table". This will add a new and empty table object item to the Query Building Area:

Right-click the object's to bring up its context menu:

Select "Switch to derived table" to add a new tab to the Query Building Area:

The same functions, operations, and restrictions that apply to the main query applies to a derived table.

Use the Customers table as our base table for your derived table. Switching back to the Main tab of the VQE, the following screen will be displayed:

The derived table is now filled and joins Orders with [Order Details]. To relate Query1 to one or more of the other tables, drag the Query1 column to another table object and drop it over the target column. The relationship is now established:

Adding UNION queries
UNION queries are added in pretty much the same fashion as derived tables are added. Just open the Query Building Area's context menu and select "Union" -> "New Union Sub-Query":

This again will result in a new tab opened in which you can generate your UNION query:

Directly editing the SQL source code
Again, the VQE in ApexSQL Edit is a two-way tool. Hence, editing the generated SQL code also affects its visual representation. Suppose you have an empty UNION sub-query in the Query Building Area, the the SQL code in the SQL Output Area will look like this:

To remove it, simply remove it from the code. This modification immediately shows in the Query Building Area.

Conclusion
The VQE offers many advanced features that can't be found in other tools and it simplifies the writing of complex queries.

AUTHOR
Frank Kirkland

LAST REVIEW DATE
13 August 2007

Labels: Apex SQL Edit

posted by Janice Lee on Saturday, September 01, 2007

0 Comments: