Join Wintertainment 2021 to share your stories, have fun, earn community badges, and more!

Designing SQL Queries

Applies to LoadComplete 4.97, last modified on May 20, 2019

The SQL Designer allows you to visually create, view and test SQL queries for database variables. You can also use it to explore tables, views and fields available in the selected data source.

The SQL Designer can be opened by clicking the Design button on the Enter SQL Query page of the wizard.

Below is a sample image of the SQL Designer’s window:

SQL Designer

Panels

The Designer contains the following panels:

  • Tables - Displays the tables and views that exist in the database.

  • SQL - Contains the SQL text of the created query. This text is automatically updated when you modify the query in the Designer. You can also type the text into this panel.

  • Results - Displays the results of the query execution.

  • Working area - This panel is not labeled. It displays the tables and views that were included into the query.

The panel layout is not strictly defined. You can dock or undock the panels within the Designer window, change their width or height and perform other operations in the same manner as you would with panels within LoadComplete main window (see Docking). If you hide a panel, you can display it by selecting the appropriate item from the Designer’s toolbar or from the Docking menu.

Creating Query

You create a query in the following way:

  1. Add the desired tables or views from the Tables panel to the working area.

  2. Select the fields to be used in the query.

  3. Create joins between the tables.

  4. Execute the query and check the returned data.

The following sections provide detailed descriptions of how you can perform these operations:

To add a table or view

To remove a table or view

To specify an alias for a table or view

To specify the fields for the query

To specify an alias for a field

To join tables

To set the join properties

To execute the query

To specify the selecting, sorting and grouping conditions

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.

Specifics

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, LoadComplete 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.

See Also

Database Variables

Highlight search results