You create a query in the following way:
-
Add the desired tables or views from the Tables panel to the working area.
-
Select the fields to be used in the query.
-
Create joins between the tables.
-
Execute the query and check the returned data.
The following sections provide detailed descriptions of how you can perform these operations:
To add or remove a table or view
To add or remove a table or view
To add or remove a table or viewTo add a table or view to the query:
– or –
TestComplete will display the list of table’s (or view’s) fields in the work area.
After adding the table (or view), you can select the fields to be used in the query or specify an alias for the table (or view).
You can arrange the tables and views within the working area by selecting SQL > Rearrange from the Designer’s main menu or by pressing 
Rearrange from the toolbar.
To remove a table or view from the query:
You can also add tables and views to or remove them from the query by specifying the table names in or deleting them from the FROM clause of the SELECT command:
SQL
SELECT
Table1.Name,
Table2.Date
FROM
Table1, Table2
WHERE
...
The Designer will update the visual scheme of the query when you exit the SQL panel. You can also update the visual scheme by pressing 
Update on the Designer’s toolbar.
To specify an alias for a table or view
To specify an alias for a table or viewTo specify an alias for a table or viewTo specify an alias for a table or view:
-
Add the desired table (or view) to the query.
-
Right-click the table’s (or view’s) title in the working area and select 
Edit Table Alias from the context menu.
– or –
Double-click the table (or view) title in the working area.
This will invoke the Edit Table dialog.
-
In the dialog, specify the desired alias in the Alias text box and press OK to apply the changes.
When you specify an alias for the query, the Designer display the alias in square brackets after the table title in the working area.
You can also specify the table alias by changing the query’s code in the SQL panel, for instance:
SQL
SELECT
Tbl.FirstName,
Tbl.SecondName
FROM
MySuperTable "Tbl"
The Designer will update the visual scheme of the query when you exit the SQL panel. You can also update the visual scheme by pressing Update on the Designer’s toolbar.
To specify the fields for the query
To specify the fields for the queryTo specify the fields for the queryTo choose the fields, whose values will be returned by the query:
-
Add the table (or view) to the query.
SQL Designer will display the box that contains the tables’ fields list in the working area.
-
Select the check box for the appropriate field in the list.
To include all fields, select the * item in the list.
To exclude a field from the query, clear the field’s check box in the list. To exclude all fields in the table (or view), clear the check box of the * item.
You can also specify the retrieved fields by typing their names into the SQL panel after the SELECT clause, for instance:
SQL
SELECT
Table1.FirstName,
Table1.SecondName
FROM
Table1
To exclude a field from the query, you can remove its name from the SELECT clause.
SQL Designer will automatically update the check boxes in the work area after you exit the SQL panel. You can also update the visual scheme by pressing 
Update on the Designer’s toolbar.
Notes:
-
It is not allowed to specify fields, whose names coincide with SQL statements. For instance, you cannot use the fields, whose names are Select or From. To include these fields into the query, you have to create aliases for them.
-
The query cannot contain two or more fields that have the same name. To work around the problem, create aliases for duplicated field names.
To specify an alias for a field
To specify an alias for a fieldTo specify an alias for a field
-
Add the desired table or view to the query.
-
Right-click the desired field in the work area and choose 
Edit Field Alias from the context menu.
– or –
Double-click the desired field in the work area.
This will invoke the Edit Field dialog.
-
Specify the desired alias into the Alias text box of the Edit Field dialog and click OK to apply the changes.
SQL Designer will update the query text and display the alias in square brackets after the field name in the work area.
You can also specify an alias for a field by modifying the query text in the SQL panel. You do this by using the AS clause, for instance:
SQL
SELECT
Table1.Name AS Name1,
Table2.Name AS Name2
FROM
Table1
INNER JOIN ...
The Designer will update the visual scheme of the query when you exit the SQL panel. You can also update the visual scheme by pressing Update on the Designer’s toolbar.
| Note: |
The alias serves as a new field name in the query. It should not coincide with other field names and aliases. |
To join tables
To join tablesTo join tablesThe tables included in the query must be joined. The queries that do not contain joins are not supported.
In SQL, you can join the tables by using the WHERE clause of the SELECT command or by using the INNER JOIN and OUTER JOIN operators.
SQL Designer builds the queries that use JOIN operators:
-
Add the desired tables (or views) to the query.
-
Drag a field from one table (or view) and drop it to the field of another table (or view).
The Designer will add the join expression to the query and modify the query text in the SQL panel. To indicate the join visually, the Designer will link the tables in the working area with a line.
After creating a join, you can modify its properties. For more information on this, see the next section.
To delete a join, right-click the join line in the work area and choose Remove from the context menu.
To join the tables with the WHERE clause, you should modify the query code in the SQL panel, for instance:
SQL
SELECT
Table1.ID,
Table2.EmpID
FROM
Table1,
Table2
WHERE
Table1.ID = Table2.EmpID
The Designer will update the visual scheme of the query when you exit the SQL panel. You can also update the visual scheme by pressing Update on the Designer’s toolbar.
To set the join properties
To set the join propertiesTo set the join propertiesTo modify the properties of a join:
-
Right-click the join line in the working area and choose Edit from the context menu.
This will invoke the Edit Join dialog, in which you can modify the join properties.
-
In the dialog, you can specify the join condition and specify which table rows should be included into the query. For more information on this, see the dialog description.
To execute the query
To execute the queryTo execute the queryTo execute the specified query, choose SQL > Execute SQL from the main menu of the SQL Designer window, or choose 
Execute SQL from the Designer’s toolbar, or press F5.
The Designer will execute the query and display the retrieved data in the Results panel. By reviewing the data you can determine whether the query functions as expected. If it is, you can save the query and close the designer.
| Note: |
When you command the Designer to execute a query, the Designer passes the query to the database provider, which you specified when creating the checkpoint or variable. The provider executes the query and returns the results to the Designer. That is, the query is not executed by the TestComplete SQL Designer or any other TestComplete subsystem. The Designer serves only for creating the query code and displaying the results. |
To specify the selecting, sorting and grouping conditions
To specify the selecting, sorting and grouping conditionsTo specify the selecting, sorting and grouping conditionsThe SELECT command may contain the WHERE, ORDER BY, GROUP BY and other clauses. SQL Designer does not include features for visual creation of these clauses. To add them to your query, modify the query code in the SQL panel. The Designer can recognize these clauses. It will save and load them correctly.
For detailed information on the clauses, see SQL documentation.
To save the changes made to the query in the Designer, choose File > Save and close from SQL Designer’s main menu or press 
Save and close from the toolbar.
To close the designer without saving the changes, select File > Exit.
When you open a query in the Designer, the Designer parses the query code and builds a visual scheme in the work area. If the Designer cannot build a visual scheme, TestComplete displays an error message and the designed is not shown.
SQL Designer also parses the query code when you change it in the SQL panel. If the code was parsed successfully, the Designer updates the visual scheme. If the parsing fails, Designer displays an error message notifying you about the problem.
The Designer only supports specific types of queries:
-
The Designer supports creation of SELECT queries only. It does not support the queries of the UPDATE, DELETE or INSERT types.
-
The Designer does not support visual creation of complex SELECT queries that include nested queries. However, you can create these queries by typing their code in the SQL panel.
-
Tables in the query must be joined. The Designer’s parser does not support the queries that broke this condition.
-
The Designer’s parser does not support SQL statements and clauses that are specific to database providers. For instance, the parser does not support clauses specific to Microsoft SQL Server or Oracle database engines.
-
The query cannot contain the fields, tables or views, whose names coincide with clauses of SQL SELECT command. For instance, the query cannot contain the fields From or Select or a table name Where. A possible workaround is to create an alias for these fields, tables and views.
