Creating a query in SQL code

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

This content has been translated automatically. Click here to view the French version.

Help / Editors / Query editor / Creating queries

Overview
How to?
Creating a query in SQL code
Reverse engineering of query in SQL code
Execution modes of a query in SQL code

WINDEV

WEBDEV

WINDEV Mobile

Others

See also

Creating a query in SQL code

Overview

The query editor also allows you to create a query by typing SQL code (if you are familiar with this language).

Reports & Queries software cannot be used to create queries in SQL code using the INSERT, UPDATE or DELETE commands.

For more details on SQL, see the SQL commands available in an SQL query.

How to?

Creating a query in SQL code

To create a query in SQL code:

Click in the quick access buttons.
- In the window that appears, select "Query".
- The query creation wizard opens.
Specify that you want to create a query by entering the SQL code directly ("SQL code" option).
If your project is linked to no analysis, specify the analysis to which the query will be attached.
Finish the wizard screen.
Type the query name (name of ".WDR" file corresponding to the query). This name will be used to identify the query in your programs.
Specify the caption of the query by briefly describing the purpose of query. This caption will be used in the project documentation.
Note: The query name is defined automatically from the label entered.
Validate the query creation. A code window is displayed.
Type the SQL code of your query. You can insert into the query:
- comments by writing the "--" or "//" character in front of them. Only the comments about the selection conditions will be taken into account during the reverse engineering of the query: the other comments will be automatically deleted. The comments about selection conditions with parameters will not appear when the query is tested (via "GO").
- WLanguage functions. The WLanguage functions must be prefixed by "WL.". Assisted input is available. The WLanguage functions can be nested. Example:
  SELECT Customer.CustomerNum FROM Customer
  WHERE WL.LEFT(Customer.ZipCode,2)=75
  
  You cannot execute queries with SQL code containing WLanguage functions.
Save the SQL code ().

Note: Clicking on an identifier (file name, item name, parameter name) in the query's SQL code will hightlight all occurrences of that identifier.

Reverse engineering of query in SQL code

Retroanalysis of the query in SQL code allows the query to be graphically represented from the data files found in the SQL code and in the project analysis.

To perform the reverse engineering of a query in SQL code:

Display the SQL code of the query.
To represent the query graphically, go to the "Query" tab, "Analyze" group and click "Reverse Engineering".

Limitations:

This feature is available for the SELECT, UPDATE, DELETE and INSERT queries.
Sub-queries are not imported.
Queries of queries are not supported.
UNION queries are not supported.
This feature is available only if the SQL commands used in the query are recognized by the HFSQL engine. For more details, see the SQL commands available in an SQL query.

Execution modes of a query in SQL code

The query editor allows you to easily execute queries in SQL code. These queries can be:

written in the query editor. The query is then executed with HExecuteQuery.
written directly in the HExecuteSQLQuery function in the code editor.

Two execution modes are available for SQL queries:

SQL query execution with SQL code verification This option is available by default for all SQL queries executed from WINDEV. The SQL code of the query must use the commands listed below. In this case, the HFSQL engine checks the SQL code of the query.
Execute SQL query without checking SQL code code (using the constant hQueryWithoutCorrection): This option is recommended for queries executed via native Access or OLE DB. In this case, the HFSQL engine does not check the query. This option must be used if the query contains commands specific to a connection type (Oracle, SQL Server, etc.). All the SQL commands supported by the accessed database can be used.

Note The constant hQueryWithoutCorrection constant is also available when testing a query from the query editor:

for a selection query For a selection query: click on the "Advanced" button in the description window and check the "Run with hQueryWithoutCorrection in test mode" option in the "hQueryWithoutCorrection" tab.
for an insertion, modification or deletion request For example, if you want to insert, modify or delete a query, display the "General" tab of the description window and check the "Run with hRequestWithoutCorrection in test mode" option.