PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • 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
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
Overview
Once the connection to the database is established, WDSQL allows you to create different types of SQL queries:
  • Select query (SELECT command): show a selection of records in the database.
  • Insert query (INSERT command): add records into a database file.
  • Update query (UPDATE command): modify the records found in a database file.
  • Delete query (DELETE command): delete records from a database file.
Creating an SQL query
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 (select "File .. Save a query" or press [F4] or click ). 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 a 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:
  • Viewing and/or using the items found in the current database: click the icon. See Database structure for more details.
  • Viewing and/or using the different SQL keywords: click 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:
  1. Select "File .. Create a query" (or click ).
  2. In the wizard, select "A Select query" and go to the next step.
  3. Select the different query items.
    Note: If the current database is an Oracle database or a SQL Server database, you have the ability to view the tables created by a specific user. To do so, select the user in the "User" combo box.
  4. Define the selection conditions for each item.
  5. Sort the records according to one or more items:
    • In ascending order: click the "Sort" column corresponding to the sort items. The icon appears.
    • In descending order: click the "Sort" column corresponding to the sort items. The icon appears.
  6. 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 the customers who placed at least one order, the customers who placed several orders will appear once only in the query result.
  7. If sort items have been defined, specify the sequence of these sort items. See Defining several sort items for more details.
  8. 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:
  1. Select "File .. Create a query" (or click ).
  2. In the wizard, select "An Insert query" and go to the next step.
  3. Select the data file into which the data will be inserted. The list of file items is automatically displayed.
    Note: If the current database is an Oracle database or a SQL Server database, you have the ability to view the tables created by a specific user. To do so, select the user in the "User" combo box.
  4. 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:
  1. Select "File .. Create a query" (or click ).
  2. In the wizard, select "An Update query" and go to the next step.
  3. Select the data file in which the data will be modified. The list of file items is automatically displayed.
    Note: If the current database is an Oracle database or a SQL Server database, you have the ability to view the tables created by a specific user. To do so, select the user in the "User" combo box.
  4. Enter the new values in the "Value" column.
  5. 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:
  1. Select "File .. Create a query" (or click ).
  2. In the wizard, select "A Delete query" and go to the next step.
  3. Select the data file from which the data will be deleted. The list of file items is automatically displayed.
    Note: If the current database is an Oracle database or a SQL Server database, you have the ability to view the tables created by a specific user. To do so, select the user in the "User" combo box.
  4. 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 total amount of orders (ascending 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 CUSTOMER.CustomerLName, CUSTOMER.City, ORDERS.TotalIOT
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.City LIKE 'Paris'
ORDER BY TotalIOT ASC, City DESC, CustomerName ASC
Minimum required version
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment