|
|
|
|
|
|
|
|
|
|
- Overview
- Creating an SQL query
- Creating an SQL query from the main window of WDSQL
- To create a query from the main window of WDSQL
- Creating an SQL query from the wizard
- Defining several sort items
WDSQL: Creating a SQL query
Once the connection to the database is established, WDSQL allows you to create different types of SQL queries: - Selection query (SELECT statement): display a selection of records in the database.
- Insert request (INSERT statement): add records to a data file in the database.
- Modification request (UPDATE statement): modification of records in a database file.
- Delete request (DELETE statement): deletes records from a database file.
You have the ability to create queries: - From the main window of WDSQL.
- From the wizard for query creation.
Once created, these queries can be: - Saved ("File .. Save a query" or F4 or
 ). The extension of created file is ".SQL". - Run.
- Printed (click
 ). - Modified in WDSQL directly.
Caution: A query created in WDSQL cannot be modified in WINDEV.. To use a query created in WDSQL, all you have to do is copy/paste the SQL code: - to an SQL code window in WINDEV,
- into the "Text of SQL query" parameter of HExecuteSQLQuery.
Creating an SQL query from the main window of WDSQL To create a query from the main window of WDSQL WDSQL proposes different methods to help you create the query in SQL: - To view and/or use existing fields in the current database, click on the
 icon.. For more details, see Database structure. - To view and/or use the various SQL keywords, click on the
 icon.
Creating an SQL query from the wizard For the following operation, we consider that the connection to the database was established. To create a Select query with the wizard: - Select "File .. Create a query" (or click
 ). - In the wizard, select "A Select query" and go to the next step.
- Select the different query items.
Remark: If the current database is an Oracle or SQL Server database, it is possible to view the tables created by a given user.. To do so, select the user in the "User" combo box. - Define the selection conditions for each item.
- Sort the records according to one or more items:
- In ascending order: click in the "Sort" column corresponding to the sort item(s).. The
 icon appears. - In descending order: click in the "Sort" column corresponding to the sort item(s).. The
 icon appears.
- If "Do not display the duplicates" is checked, each record found in the query result will be unique.
For example, if a query is used to display all customers who placed at least one order, the customers who placed several orders will appear once only in the query result. - If sort items have been defined, specify the sequence of these sort items. For more details, see Defining several sort items.
- Validate. The SQL code of the created query is automatically displayed in the main window of WDSQL.
For the following operation, we consider that the connection to the database was established. To create an Insert query with the wizard: - Select "File .. Create a query" (or click ).
- In the wizard, select "An Insert query" and go to the next step.
- Select the data file into which the data will be inserted. The list of file items is automatically displayed.
Remark: If the current database is an Oracle or SQL Server database, it is possible to view the tables created by a given user.. To do so, select the user in the "User" combo box. - Enter the new values to insert in the "Value" column and validate. The SQL code of the created query is automatically displayed in the main window of WDSQL.
For the following operation, we consider that the connection to the database was established. To create an Update query with the wizard: - Select "File .. Create a query" (or click ).
- In the wizard, select "An Update query" and go to the next step.
- Select the data file in which the data will be modified. The list of file items is automatically displayed.
Remark: If the current database is an Oracle or SQL Server database, it is possible to view the tables created by a given user.. To do so, select the user in the "User" combo box. - Enter the new values in the "Value" column.
- Define the selection conditions for each record to modify and validate. The SQL code of the created query is automatically displayed in the main window of WDSQL.
For the following operation, we consider that the connection to the database was established. To create a Delete query with the wizard: - Select "File .. Create a query" (or click ).
- In the wizard, select "A Delete query" and go to the next step.
- Select the data file from which the data will be deleted. The list of file items is automatically displayed.
Remark: If the current database is an Oracle or SQL Server database, it is possible to view the tables created by a given user.. To do so, select the user in the "User" combo box. - Define the selection conditions for each record to delete and validate. The SQL code of the created query is automatically displayed in the main window of WDSQL.
Defining several sort items When several sort items are defined, the sorts are nested. The sort starts from the first item found in the table of sort items. For example, when creating the query, in the window below, the result of the query is sorted: - by the total amount of the orders (increasing order),
- then by city of customers (descending order),
- finally by name of customers (ascending order).
In the next wizard step, the arrows found on the right of the window are used to modify the sequence of the sort items. The SQL code corresponding to this example will be as follows: SELECT CLIENT.NomClient, CLIENT.Ville, COMMANDE.TotalTTC
FROM CLIENT, COMMANDE
WHERE CLIENT.Ville LIKE 'Paris'
ORDER BY TotalTTC ASC, Ville DESC, NomClient ASC
This page is also available for…
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|