SQL commands available in an SQL query

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

Help

Editors

Query editor

SQL

SQL commands available in an SQL query

Overview

Available commands

See also

SQL commands available in an SQL query

Overview

WINDEV, WEBDEV and WINDEV Mobile allow you to easily run 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.

Two runtime modes of SQL queries are available in WINDEV, WEBDEV and WEBDEV Mobile:

Executing an SQL query while checking the SQL code: this option is available by default for all the SQL queries run from WINDEV, WEBDEV and WINDEV Mobile. 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.
Running an SQL query without checking the SQL code (with the hQueryWithoutCorrection constant): This option is recommended for queries executed via a 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.

Remark: The hQueryWithoutCorrection constant is also available when the query test is run from the query editor:

for a Select query, click the "Advanced" button of the description window. In the "hQueryWithoutCorrection" tab, check "Run with hQueryWithoutCorrection in test mode". You have the ability to choose the connection that will be used to run the query in test mode.
for an Insert query, an Update query or a Delete query, display the "General" tab of the description window and check "Run with hQueryWithoutCorrection in test mode". Then, select the connection that will be used in test mode.

Available commands

The "SQL commands" (statements, functions, clauses, ...) that can be used in an SQL query checked by the HFSQL engine are as follows:


ADD_MONTHS	Adds months to the specified date.
ALL	Used with the SELECT statement Selects all records. Used with the UNION operator Selects the common records. Used in the sub-queries.
AND	Combines several selection conditions (both).
ANY	Used in the sub-queries
AS	Creates an alias for each item and file used.
ASC	Used with the ORDER BY clause Defines the ascending order.
ASCII	Returns the ASCII code: of a character. of first character found in a string.
ATAN2	Returns the arctangent of X and Y variables.
AVG	Calculates the mean for a set of values.
BEGIN	Starts a transaction.
BEGIN WORK	Starts a transaction.
BEGIN TRANSACTION	Starts a transaction.
BETWEEN	Selects the records for which the value of an item belongs to a range of values.
BIN	Returns a binary representation of a number, as a string value.
BITAND	Performs a bitwise AND operation on two numeric expressions.
BITANDNOT	Performs bitwise AND and NOT operations on two numeric expressions.
BITNOT	Performs a bitwise negation on a numeric expression.
BITOR	Performs a bitwise OR operation on two numeric expressions.
BITXOR	Performs a bitwise XOR operation on two numeric expressions.
BOTTOM	Returns the last n records found in the result of a query.
BTRIM	Deletes a string within another string from the beginning or from the end.
CASE	Returns the value corresponding to the condition.
CAST	Used to convert a number into another type.
CBRT	Returns the cube root of a number.
CEILING CEIL	Returns the smallest integer value that is larger than or equal to a number.
CHAR_LENGTH	Returns the length (number of characters) of an expression.
CHARACTER_LENGTH	Returns the length (number of characters) of an expression.
COALESCE	Returns the first not-null expression among its arguments.
COMMIT	Validates a transaction.
COMMIT TRANSACTION	Validates a transaction.
COMMIT WORK	Validates a transaction.
CONCAT	Concatenates multiple strings.
CONVERT	Converts a character string from a character set to another one.
COUNT	Returns the number of records in a file or in a group of records.
CREATE TABLE	Describes and creates a file/table.
CURRENT_TIMESTAMP	Returns the date and the local time of server.
CURRENT_USER	Returns the username for the current connection.
DATEADD	Adds a value to the start date and returns the new date.
DATEDIFF	Calculates the difference between two dates in a given unit. The return value is a signed integer.
DATEDIFFBIG	Calculates the difference between two dates in a given unit. The return value is a signed big integer.
DATEFROMPARTS	Returns a date from the specified parts.
DATETIMEFROMPARTS	Returns a datetime from the specified parts.
DATEPART	Returns an integer that corresponds to a specified part of a given datetime.
DAY	Returns the day of the month, i.e. a number between 1 and 31.
DAYOFMONTH	Returns the day of the month (between 1 and 31).
DAYOFWEEK	Returns the day in the week (1 for Sunday, 2 for Monday, etc.).
DAYOFYEAR	Returns the day of the year (between 1 and 366).
DECODE	Returns the value corresponding to the condition.
DEGREES	Converts a value in radians to degrees.
DELETE	Deletes records from a file.
DESC	Used with the ORDER BY clause Defines the descending order.
DISTINCT	Used with the SELECT statement Deletes the duplicated records (duplicates) from the result of the query.
DIV	Performs a division.
DROP TABLE	Deletes a file/table physically.
ELT	Returns the nth string of a list of strings.
EOMONTH	xx
EVERY	Returns True if all the arguments are checked and true.
EXISTS	Used in the sub-queries
FIELD	Returns the index of the string that must be found in the list.
FIND_IN_SET	Returns the position of a string in a list of values.
FROM	Used with the SELECT statement Defines the files used. Used with the DELETE statement Defines the files used.
FULL OUTER JOIN	Performs an outer join.
GETDATE	Returns the date and the local time of server.
GETUTCDATE	Returns the date and the UTC time of server.
GREATEST	With two or more arguments, returns the greatest value.
GROUP BY	Classifies a selection of records by group.
New in version 28 GROUP_CONCAT	Concatenates non-null values of an item from a series of records into a string.
HAVING	Used to specify one or more conditions on groups of records generated by the GROUP BY statement.
HEX	Returns the hexadecimal value of an integer number (expressed in base 10).
IN	Selects the records whose values correspond to a specified list of values. Used in the sub-queries.
INITCAP	Returns a string containing the first letter of each word in uppercase characters.
INNER JOIN	Performs an inner join.
INSERT	Adds a record to a data file.
INSTR	Returns the position of a character string.
INTERSECT	Allows you to combine selection queries (SELECT statement).
INTO	Used with the INSERT statement Defines the file used.
ISDATE	Allows you to determine if an expression corresponds to a date.
IS JSON	Allows you to determine if an item is: a JSON content (IS JSON), a JSON content that represents an object (IS JSON OBJECT), a JSON content that represents an array (IS JSON ARRAY),
ISNULL / IFNULL	Selects the records for which the value of an item is null.
JSON_OBJECT	Retrieves a JSON object from any item.
JSON_OBJECTAGG	Returns a JSON object containing key-value pairs for each specific key and value in a set of SQL values.
JSON_ARRAY	Retrieves a JSON array from any item. The JSON content retrieved corresponds to an array.
JSON_ARRAYAGG	Returns a JSON array containing key-value pairs for each specific key and value in a set of SQL values.
JSON_EXISTS	Retrieves records with a JSON item containing data.
JSON_QUERY	Retrieves a JSON object or array.
JSON_VALUE	Retrieves the value of an element contained in the JSON item.
LAST_DAY	Calculates the date of last day for the specified month.
LAST_INSERT_ID	Returns the last automatic identifier calculated.
LCASE	Returns a string in which all uppercase characters have been converted to lowercase.
LEAST	With two or more arguments, returns the lowest value.
LEFT	Extracts the first characters from the content of an expression.
LEFT OUTER JOIN	Performs an outer join
LEN	Returns the length (number of characters) of an expression.
LENGTH	Returns the length (number of characters) of an expression.
LIKE	Selects the records for which the value of an item matches a specified value (with wildcard characters).
LIMIT	Returns part of the query result.
LOWER	Converts the content of an expression to lowercase characters.
LPAD	Returns a string of a given size. To reach the requested size, the string is left-padded: by space characters. by a character or by a given string.
LTRIM	Returns a character string: without space characters on the left. without a list of characters.
MATCH AGAINST	Used to get the relevance index of the record during a full-text search.
MAX	Returns the greatest value of an item for all selected records in the file.
MD5	Calculates the MD5 checksum of the string.
MID	Extracts a substring from a given position.
MIN	Returns the lowest value of an item for all selected records in the file.
MONTH	Returns the month of a date.
MONTHS_BETWEEN	Returns the number of months between two dates.
NEW_TIME	Returns the date after time zone conversion.
NEXT_DAY	Returns the first day of the week based on the specified date or day.
NOT	Reverses the meaning of the logical operator used. Used in the sub-queries.
NVL	Replaces the null values of the column by a substitution value.
OCT	Returns the octal value of an integer number (expressed in base 10).
OCTET_LENGTH	Returns the length (number of characters) of an expression.
ON	Used when creating a join.
OR	Combines several selection conditions (one or the other).
ORDER BY	Sorts the selected records.
OVERLAY	Replaces a string in another string.
PATINDEX	Returns the position of the first occurrence of a character string.
POSITION	Returns the position of a character string in an expression.
RADIANS	Converts a degree value into radians.
RANDOM/RAND	Returns a random number between 0.0 and 1.0 (inclusive).
REGEXP	Evaluates a regular expression inside an SQL query.
REPEAT	Returns a string containing a string that is repeated a specified number of times.
REPLACE	Returns a character string: by replacing all the occurrences of a word found in a string by another word. by replacing all occurrences of a word found in a string.
REVERSE	Returns a string in which the character order of a specified string is reversed.
RIGHT	Extracts the last characters from the content of an expression.
RIGHT OUTER JOIN	Performs an outer join.
REGEXP_LIKE	Evaluates a regular expression inside an SQL query.
ROLLBACK	Cancels a transaction.
ROLLBACK TO SAVEPOINT	Rolls back all actions performed within the transaction since the savepoint was established.
ROLLBACK TRANSACTION	Cancels a transaction.
ROLLBACK WORK	Cancels a transaction.
ROUND	Returns the rounded date in the specified format.
RPAD	Returns a string of a given size. To reach the requested size, the string is right-padded: by space characters. by a character or by a given string.
RTRIM	Returns a character string: without space characters on the right. without a list of characters.
SAVEPOINT	Creates a savepoint in the current transaction.
SELECT	Find records in one or more files.
SET	Used with the UPDATE statement Defines the name of the items used.
SET ISOLATION	Defines the isolation level of transactions.
SHA/SHA1	Calculates the 160-bit SHA1 checksum of the string, as described in RFC 3174 (Secure Hash Algorithm).
SHIFT_LEFT_OP	Bitwise operator: <<
SHIFT_RIGHT_OP	Bitwise operator: >>
SOME	Used in the sub-queries
SOUNDEX, SOUNDEX LIKE	Returns the phonetic value
SOUNDEX2, SOUNDEX2 LIKE	Returns the phonetic value (adapted to French).
SPACE	Returns a string containing N spaces.
SPLIT_PART	Divides the string according to a separator and returns the nth part.
START TRANSACTION	Starts a transaction.
STDDEV	Used to get the standard deviation of a series of values representing a sample of data.
STDDEV_POP	Used to get the standard deviation of a series of values representing the full set of data.
STDDEV_SAMP	Used to get the standard deviation of a series of values representing a sample of data.
STRING_AGG	Used to concatenate non-null strings from a list of values.
SUBSTR	Extracts a substring from a given position.
SUBSTRING	Extracts a substring from a given position.
SUM	Returns the total sum of the values of an item for all selected records in the file.
SYSDATE	Returns the current date and time.
SYSTEM_USER	Returns the username for the current connection.
TO_CHAR	Formats a date or a number.
TOP	Returns only the first n records of a query result.
TRANSLATE	Returns a character string with all the specified characters replaced with other characters.
TRIM	Returns a character string: without space characters on the left and on the right. without a character string found at the beginning and at the end of string. without a character string found at the beginning of string. without a character string found at the end of string.
TRUNC	Returns the truncated date in the specified format.
UCASE	Returns a string in which all lowercase characters have been converted to uppercase.
UNHEX	Contrary of HEX. Each pair of hexadecimal digits is interpreted as numbers and converted into a character represented by the number.
UNICODE	Returns the integer value defined by the Unicode standard of the first character in the specified expression.
UNION	Performs combining queries.
UPDATE	Updates the file records.
UPPER	Converts the content of an expression to uppercase characters.
UUID	Generates a UUID (Unique Universal Identifier). A UUID is 128 bit-integer represented as a hexadecimal string in the following format: aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee. A UUID is guaranteed to be unique at each call to the function.
USER_NAME	Returns the username for the current connection.
VALUES	Used with the INSERT statement Defines the added values.
VARIANCE	Returns the variance of a series of values (data sampling).
VAR_POP	Returns the variance of a series of values (full data set).
VAR_SAMP	Returns the variance of a series of values (data sampling).
WHERE	Used to specify the selection conditions of a query.
XOR	Logical XOR (exclusive OR).
YEAR	Returns the year of a date.