SQL commands available in an SQL query

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

Overview
Available commands

WINDEV

WEBDEV

WINDEV Mobile

Others

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 a 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 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.

Remark: From version 19, HFSQL is the new name of HyperFileSQL.

Available commands

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


ADD_MONTH	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.
Versions 16 and later ATAN2 New in version 16 ATAN2 ATAN2	Returns the arctangent of X and Y variables.
AVG	Calculates the mean for a set of values.
Versions 21 and later BEGIN New in version 21 BEGIN BEGIN	Starts a transaction.
Versions 21 and later BEGIN WORK New in version 21 BEGIN WORK BEGIN WORK	Starts a transaction.
Versions 21 and later BEGIN TRANSACTION New in version 21 BEGIN 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.
BOTTOM	Returns the last n records found in the result of a query.
Versions 19 and later BTRIM New in version 19 BTRIM BTRIM	Deletes a string within another string from the beginning or from the end.
CASE	Returns the value corresponding to the condition.
Versions 17 and later CAST New in version 17 CAST CAST	Used to convert a number into another type.
Versions 15 and later CBRT New in version 15 CBRT CBRT	Returns the cube root of a number.
CEILING Versions 15 and later CEIL New in version 15 CEIL CEIL	Returns the rounded value of 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.
Versions 21 and later COMMIT New in version 21 COMMIT COMMIT	Validates a transaction.
Versions 21 and later COMMIT TRANSACTION New in version 21 COMMIT TRANSACTION COMMIT TRANSACTION	Validates a transaction.
Versions 21 and later COMMIT WORK New in version 21 COMMIT WORK COMMIT WORK	Validates a transaction.
CONCAT	Concatenates multiple strings.
Versions 21 and later CONVERT New in version 21 CONVERT 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.
Versions 22 and later CURRENT_TIMESTAMP New in version 22 CURRENT_TIMESTAMP CURRENT_TIMESTAMP	Returns the date and the local time of server.
Versions 22 and later CURRENT_USER New in version 22 CURRENT_USER CURRENT_USER	Returns the username for the current connection.
Versions 21 and later DAY New in version 21 DAY DAY	Returns the day of the month, which means a number included between 1 and 31.
Versions 21 and later DAYOFMONTH New in version 21 DAYOFMONTH DAYOFMONTH	Returns the day in the month (included between 1 and 31).
Versions 21 and later DAYOFWEEK New in version 21 DAYOFWEEK DAYOFWEEK	Returns the day in the week (1 for Sunday, 2 for Monday, etc.).
Versions 21 and later DAYOFYEAR New in version 21 DAYOFYEAR DAYOFYEAR	Returns the day in the year (included between 1 and 366).
DECODE	Returns the value corresponding to the condition.
Versions 15 and later DEGREES New in version 15 DEGREES 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.
Versions 15 and later DIV New in version 15 DIV DIV	Performs a division.
DROP TABLE	Deletes a file/table physically.
Versions 16 and later ELT New in version 16 ELT ELT	Returns the nth string of a list of strings.
Versions 21 and later EVERY New in version 21 EVERY EVERY	Returns True if all the arguments are checked and true.
EXISTS	Used in the sub-queries
Versions 16 and later FIELD New in version 16 FIELD FIELD	Returns the index of the string that must be found in the list.
Versions 19 and later FIND_IN_SET New in version 19 FIND_IN_SET 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.
Versions 22 and later GETDATE New in version 22 GETDATE GETDATE	Returns the date and the local time of server.
Versions 22 and later GETUTCDATE New in version 22 GETUTCDATE GETUTCDATE	Returns the date and the UTC time of server.
Versions 16 and later GREATEST New in version 16 GREATEST GREATEST	With two or more arguments, returns the greatest value.
GROUP BY	Classifies a selection of records by group.
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.
Versions 19 and later INITCAP New in version 19 INITCAP 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.
INTO	Used with the INSERT statement Defines the file used.
IS NULL / IF NULL	Selects the records for which the value of an item is null.
LAST_DAY	Calculates the date of last day for the specified month.
Versions 15 and later LAST_INSERT_ID New in version 15 LAST_INSERT_ID LAST_INSERT_ID	Returns the last automatic identifier calculated.
Versions 16 and later LCASE New in version 16 LCASE LCASE	Returns a string in which all uppercase characters have been converted to lowercase.
Versions 16 and later LEAST New in version 16 LEAST 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 completed to the left: 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.
MAX	Returns the greatest value of an item for all selected records in the file.
Versions 16 and later MD5 New in version 16 MD5 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.
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.
Versions 19 and later OVERLAY New in version 19 OVERLAY 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.
Versions 15 and later RADIANS New in version 15 RADIANS RADIANS	Converts a degree value into radians.
Versions 15 and later RANDOM New in version 15 RANDOM RANDOM/ Versions 22 and later RAND New in version 22 RAND RAND	Returns a random number between 0.0 and 1.0 (inclusive).
Versions 16 and later REPEAT New in version 16 REPEAT 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.
Versions 16 and later REVERSE New in version 16 REVERSE 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.
Versions 21 and later ROLLBACK New in version 21 ROLLBACK ROLLBACK	Cancels a transaction.
Versions 21 and later ROLLBACK TRANSACTION New in version 21 ROLLBACK TRANSACTION ROLLBACK TRANSACTION	Cancels a transaction.
Versions 21 and later ROLLBACK WORK New in version 21 ROLLBACK WORK 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 completed to the right: 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.
SELECT	Find records in one or more files.
SET	Used with the UPDATE statement Defines the name of the items used.
Versions 21 and later SET ISOLATION New in version 21 SET ISOLATION SET ISOLATION	Defines the isolation level of transactions.
Versions 16 and later SHA/SHA1 New in version 16 SHA/SHA1 SHA/SHA1	Calculates the 160-bit SHA1 checksum of the string, as described in RFC 3174 (Secure Hash Algorithm).
SOME	Used in the sub-queries
SOUNDEX, SOUNDEX LIKE	Returns the phonetic value
SOUNDEX2, SOUNDEX2 LIKE	Returns the phonetic value (adapted to French).
Versions 16 and later SPACE New in version 16 SPACE SPACE	Returns a string containing N spaces.
Versions 16 and later SPLIT_PART New in version 16 SPLIT_PART SPLIT_PART	Divides the string according to a separator and returns the nth part.
Versions 21 and later START TRANSACTION New in version 21 START TRANSACTION START TRANSACTION	Starts a transaction.
Versions 17 and later STDDEV New in version 17 STDDEV STDDEV	Used to get the standard deviation of a series of values representing a sample of data.
Versions 17 and later STDDEV_POP New in version 17 STDDEV_POP STDDEV_POP	Used to get the standard deviation of a series of values representing the full set of data.
Versions 17 and later STDDEV_SAMP New in version 17 STDDEV_SAMP STDDEV_SAMP	Used to get the standard deviation of a series of values representing a sample of data.
Versions 17 and later STRING_AGG New in version 17 STRING_AGG 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.
Versions 22 and later SYSTEM_USER New in version 22 SYSTEM_USER SYSTEM_USER	Returns the username for the current connection.
Versions 19 and later TO_CHAR New in version 19 TO_CHAR 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.
Versions 16 and later UCASE New in version 16 UCASE UCASE	Returns a string in which all lowercase characters have been converted to uppercase.
Versions 16 and later UNHEX New in version 16 UNHEX 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.
Versions 15 and later UUID New in version 15 UUID 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.
Versions 22 and later USER_NAME New in version 22 USER_NAME USER_NAME	Returns the username for the current connection.
VALUES	Used with the INSERT statement Defines the added values.
Versions 17 and later VARIANCE New in version 17 VARIANCE VARIANCE	Returns the variance of a series of values (data sampling).
Versions 17 and later VAR_POP New in version 17 VAR_POP VAR_POP	Returns the variance of a series of values (full data set).
Versions 17 and later VAR_SAMP New in version 17 VAR_SAMP VAR_SAMP	Returns the variance of a series of values (data sampling).
WHERE	Used to specify the selection conditions of a query.
Versions 16 and later XOR New in version 16 XOR XOR	Logical XOR (exclusive OR).