ONLINE HELP
 WINDEV, WEBDEV AND WINDEV MOBILE
Help / Editors / Query editor / SQL
  • Overview
  • WHERE
  • GROUP BY
  • GROUP BY with parameters
  • HAVING
  • LIMIT
  • AND / OR
  • BETWEEN
  • IN
  • IS NULL
  • LIKE
  • NOT
  • XOR
WINDEV
WindowsLinuxJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac Catalyst
Others
Stored procedures
Overview
To specify the selection conditions in an SQL query, you can use:
SQL clauses:
SQL operators:
See a specific documentation about SQL for more details.
To learn more about all the SQL commands (functions, clauses, operators, etc.) that can be used in an SQL query, see SQL commands available in an SQL query.
Remarks:
  • These clauses and operators can be used:
    • in the SQL code of queries created in the query editor. Then, these queries will be run by HExecuteQuery.
    • in the SQL code of queries run by HExecuteSQLQuery.
  • Unless stated otherwise, these clauses and operators can be used on all types of data sources (Oracle, Access, SQL Server, ...).

WHERE

The WHERE clause is used to specify the selection conditions.
Format:
WHERE SelectionCondition
Example: The following SQL code selects the Customer named "Montgomery":
SELECT *
FROM CUSTOMER
WHERE CUSTOMER.CustomerLastName = 'Montgomery'
Remark: If several conditions are specified, use the AND and/or OR operators to link these conditions.

GROUP BY

The GROUP BY clause allows you to organize a selection of records by group.
In a SELECT statement, the GROUP BY clause comes after the WHERE clause and is specified before the ORDER BY clause.
To specify conditions on groups of records generated by the GROUP BY clause, use the HAVING clause.
Format:
WHERE SelectionConditions
GROUP BY ItemNames
Example: The following SQL code is used to display the total sum of orders per customer. The result will be grouped by customer name and by city:
SELECT CUSTOMER.CustomerLastName,
CUSTOMER.City,
SUM(ORDERS.TotalIOT) AS TotalIOT
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CustNum = ORDERS.CustNum
GROUP BY CustomerLastName, City

GROUP BY with parameters

The GROUP BY clause accepts to receive values as parameter. This notion is very useful when the GROUP BY clause is based on a formula and when one of the formula elements is a variable.
Example of SQL code
SELECT
SUM(File1.Item1) AS Sum1,
File2.Item1 * {QryParam} AS Formula1,
File2.Item2 AS Item2
FROM
File1, File2
WHERE
File1.Key1 = File2.Key2
GROUP BY
File2.Item1 * {QryParam},
File2.Item2
Example of code for calling the query
Qry1.QryParam = value
HExecuteQuery(Qry1)

HAVING

The HAVING clause enables you to specify one or more conditions on groups of records generated by the GROUP BY clause.
In a SELECT statement, the HAVING clause comes after the GROUP BY clause and is specified before the ORDER BY clause.
Format:
WHERE SelectionConditions
GROUP BY NamesOfItems HAVING GroupConditions
Example: The following SQL code is used to display the total sum of orders per customer. The result will be grouped by customer name and by city. Only the customers with a total amount of orders exceeding 3,000 Euros will be considered:
SELECT CUSTOMER.CustomerLastName, CUSTOMER.City,
SUM(ORDERS.TotalIOT) AS TotalIOT
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CustNum = ORDERS.CustNum
GROUP BY CustomerLastName, City
HAVING TotalIOT > 3000

LIMIT

The LIMIT clause is used to limit the number of records returned.
In a SELECT statement, the LIMIT clause comes after the GROUP BY, HAVING and ORDER BY clauses.
Format:
WHERE SelectionConditions
GROUP BY NamesOfItems HAVING GroupConditions
LIMIT [Start,] NumberOfLines
WHERE SelectionConditions
GROUP BY NamesOfItems HAVING GroupConditions
LIMIT NumberOfLines OFFSET Start
The LIMIT clause accepts one or two numeric arguments. These arguments must be constant integers.
With one argument (NumberOfLines), the value specifies the number of lines to return from the beginning of the result set.
If two arguments are specified, the first one indicates the offset of the first record to return (from) and the second one gives the maximum number of records to return. The offset of the first record is set to 0.
Remark: LIMIT x is equivalent to LIMIT 0,x or to TOP x.
Example: Dynamic modification of the LIMIT condition of an SQL query
QRY is Data Source
sMyQry is string = [
SELECT * FROM
ZIPCODES
%1
]
 
sMyQry = StringBuild(sMyQry, "LIMIT 1, 10")
IF NOT HExecuteSQLQuery(QRY, hQueryDefault, sMyQry) THEN
Error(HErrorInfo())
ELSE
Info("The query contains " + HNbRec(QRY) + " records.")
END

AND / OR

The WHERE clause can include several conditions. In this case, the conditions are linked by the AND and/or OR operators.
Format:
SelectionConditionN AND/OR SelectionConditionN+1
Example:
  • The following SQL code is used to list the male customers who live in Paris (75):
    SELECT CUSTOMER.CustomerLastName
    FROM CUSTOMER
    WHERE CUSTOMER.Title LIKE 'Mr%'
    AND CUSTOMER.ZipCode LIKE '75%'
  • The following SQL code is used to list both:
    • the male customers (regardless of where they live).
    • the customers (men or women) living in Paris (75).
      SELECT CUSTOMER.CustomerLastName
      FROM CUSTOMER
      WHERE CUSTOMER.Title LIKE 'Mr%'
      OR CUSTOMER.ZipCode LIKE '75%'
Remark: The AND operator can also be used with the BETWEEN operator.

BETWEEN

The BETWEEN operator is used to select the records whose value for an item belongs to a range of values. The bounds are included in the result.
Format:
WHERE ItemName BETWEEN MinimumValue AND MaximumValue
Example: The following SQL code is used to list the orders placed between the 01/01/2012 and the 01/07/2012 (the dates are included in the result):
SELECT ORDERS.OrderNum
FROM ORDERS
WHERE ORDERS.OrderDate BETWEEN '20120101' AND '20120701'
Remark: To select the records that do not belong to this range of values, use the NOT operator.

IN

The IN operator is used to select the records whose values correspond to a list of specified values.
Format:
WHERE ItemName IN (Value1, Value2,..., ValueN)
Example: The following SQL code is used to list the customers who live in Montpellier, Avignon or Marseille:
SELECT CUSTOMER.CustomerLastName
FROM CUSTOMER
WHERE CUSTOMER.City IN ('Montpellier', 'Avignon', 'Marseille')
Example: The following code is used to list the customers living in Montpellier, Avignon or Marseille with a project query:
// The QRY_CityCustomerList query of the project corresponds to the code: 
// SELECT * FROM CUSTOMER WHERE CUSTOMER.City IN ({ConfigCityList}) 
QRY_CityCustomerList.ConfigCityList = "'Montpellier';'Avignon';'Marseille'"
// or QRY_CityCustomerList.ConfigCityList = "Montpellier;Avignon;Marseille"
HExecuteQuery(QRY_CityCustomerList)
Info(HNbRec(QRY_CityCustomerList) + " Customers in the selected cities.")
Remarks:
  • To select the records that do not belong to this list of values, use the NOT operator.
  • The specified values can correspond to a parameter (variable, control name, ...). In this case, the values found in this parameter must be separated by a semicolon (';'), a carriage-return ('RC') or a tabulation ('TAB').

IS NULL

The IS NULL operator is used to select the records whose value for an item is null.
Format:
WHERE ItemName IS NULL
Example: The following SQL code is used to select the products that do not belong to a family of products:
SELECT PRODUCT.ProductName
FROM PRODUCT
WHERE PRODUCT.Family IS NULL
Remark: To select the records whose value for an item is not null, use the NOT operator.

LIKE

The LIKE operator is used to select records in which the value of an item matches a specified constant value (using wildcard characters).
The authorized wildcard characters are:
  • '%': represents zero, one or more characters.
  • '_': represents a single character.
These generic characters can be combined.
Format:
WHERE ItemName LIKE Value
Examples:
  • The following SQL code is used to select the customers whose last name starts with the letter 'A':
    SELECT CUSTOMER.CustomerLastName
    FROM CUSTOMER
    WHERE CUSTOMER.CustomerLastName LIKE 'A%'
  • The following SQL code is used to select the customers whose last name starts with the letter 'A' and that contains at least 4 characters:
    SELECT CUSTOMER.CustomerLastName
    FROM CUSTOMER
    WHERE CUSTOMER.CustomerLastName LIKE 'A___%'
  • The following SQL code is used to select the customers whose last name contains the letter 'A':
    SELECT CUSTOMER.CustomerLastName
    FROM CUSTOMER
    WHERE CUSTOMER.CustomerLastName LIKE '%A%'
  • The following SQL code is used to select the customers whose last name ends with the letter 'A':
    SELECT CUSTOMER.CustomerLastName
    FROM CUSTOMER
    WHERE CUSTOMER.CustomerLName LIKE '%A'
Remark: To select the records whose value for an item differs from the specified value, use the NOT operator.
Using indexes in the HFSQL files
The index is used if:
  • the sought value contains no generic character ('%' or '_').
  • the sought value contains a single '%' character found in last position and no other generic character ("starts with" search).
In all the other cases, the indexes are not used.
Note: There is no difference between LIKE and NOT LIKE
Finding the _ and \ characters in LIKE:
To find the '_' or '\' characters, these characters must be preceded by an escape character. By default, this character is '\'. It can be redefined by using the ESCAPE keyword just after the LIKE clause.
Examples:
  • Searching for strings that contain "_1\": LIKE '%\_1\\%'
  • Searching for strings that contain "_1\" while redefining the escape character by @: LIKE '%@_1@\%' ESCAPE '@'
Sensitivity
The sensitivity (to the case, to the accented characters, to the punctuation, to the special characters) corresponds to the sensitivity of the key used.
If the item used is not a key item or if the operator uses no index, the comparison will be insensitive to the case and to the accented characters.
If the item used corresponds to the concatenation of several items, the comparison will be:
  • insensitive to the case if at least one of the concatenated items is not sensitive to the case.
  • insensitive to the accented characters if at least one of the concatenated items is not sensitive to the accented characters.
  • insensitive to the punctuation if at least one of the concatenated items is not sensitive to the punctuation characters.
  • insensitive to the special characters if at least one of the concatenated items is not sensitive to the special characters.

NOT

The NOT operator reverses the significance of the logical operator used:
  • NOT BETWEEN: selects the records for which the value of an item does not belong to a range of values.
  • NOT IN: selects the records whose values differ from a list of values.
  • NOT LIKE: selects records for which the value of an item is different from a specified value (with wildcard characters).
  • IS NOT NULL: selects the records whose value for an item is different from the null value.

XOR

The XOR operator corresponds to the logical exclusive OR.
  • If one of the operands corresponds to the null value, this operator returns NULL.
  • For the non-null operands, this operator returns 1 for an even number of non-null operands and 0 in the other cases.
Format:
WHERE Operand1 XOR Operand2
See also
Minimum version required
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 04/05/2024

Send a report | Local help