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 | |
AND | Combines several selection conditions (both). |
ANY | Used in the sub-queries |
AS | Creates an alias for each item and for each file used. |
ASC | Used with the ORDER BY statement Defines the ascending order. |
ASCII | Returns the ASCII code:- of a character.
- of the first character in a string.
|
Versions 16 and laterATAN2 ATAN2 | Returns the arctangent of X and Y variables. |
AVG | Calculates the mean for a set of values. |
Versions 21 and laterBEGIN 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 the character string representing "integer" in binary. |
BOTTOM | Returns the last n records found in the result of a query. |
Versions 19 and laterBTRIM BTRIM | Deletes a string found in another string from the beginning or from the end. |
CASE | Returns the value corresponding to the condition. |
Versions 17 and laterCAST New in version 17CAST CAST | Used to convert a number into another type. |
Versions 15 and laterCBRT New in version 15CBRT CBRT | Returns the cube root of a number. |
CEILING
Versions 15 and laterCEIL New in version 15CEIL CEIL | Returns the rounded value of a number. |
CHAR_LENGTH | Returns the size (number of characters) of an expression. |
CHARACTER_LENGTH | Returns the size (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 several strings together. |
CONVERT | Converts a character string from a character set to another one. |
COUNT | Returns the number of records found 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 user name for the current connection. |
Versions 21 and laterDAY New in version 21DAY DAY | Returns the day of the month, which means a number included between 1 and 31. |
DAYOFMONTH | Returns the day in the month (included between 1 and 31). |
DAYOFWEEK | Returns the day in the week (1 for Sunday, 2 for Monday, etc.). |
DAYOFYEAR | Returns the day in the year (included between 1 and 366). |
DECODE | Returns the value corresponding to the condition. |
DEGREES | Converts an angle expressed in radians into degrees. |
DELETE | Deletes records from a file. |
DESC | Used with the ORDER BY statement Defines the descending order. |
DISTINCT | Used with the SELECT statement Deletes the duplicated records (duplicates) from the result of the query. |
Versions 15 and laterDIV New in version 15DIV DIV | Performs an entire division. |
DROP TABLE | Deletes a file/table physically. |
Versions 16 and laterELT New in version 16ELT ELT | Returns the nth string of a list of strings. |
Versions 21 and laterEVERY EVERY | Returns True if all the arguments are checked and true. |
EXISTS | Used in the sub-queries |
Versions 16 and laterFIELD 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 arguments or more, 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 (expressed in base 10). |
IN | Selects the records whose values correspond to a list of values. |
INITCAP | Returns a string containing the first letter of each word in uppercase characters. |
INNER JOIN | Performs an inner join. |
INSERT | Adds a record into 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 laterLAST_INSERT_ID New in version 15LAST_INSERT_ID LAST_INSERT_ID | Returns the last automatic identifier that was calculated. |
Versions 16 and laterLCASE LCASE | Returns the str string with all the character in lowercase, according to the current set of characters. |
Versions 16 and laterLEAST LEAST | With two arguments or more, 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 size (number of characters) of an expression. |
LENGTH | Returns the size (number of characters) of an expression. |
LIKE | Selects the records for which the value of an item corresponds to a specified value (with wildcard characters). |
LIMIT | Returns part of the query result. |
LOWER | Converts the content of an expression into 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 the space characters on the left.
- without a list of characters.
|
MAX | Returns the greatest value of an item for all the records selected in the file. |
Versions 16 and laterMD5 New in version 16MD5 MD5 | Calculates the MD5 check sum of the string. |
MID | Extracts a sub-string from a given position. |
MIN | Returns the lowest value of an item for all the records selected 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 following the specified date or day. |
NOT | Reverses the meaning of the logical operator used. |
NVL | Replaces the null values of the column by a substitution value. |
OCT | Returns the octal value of an integer (expressed in base 10). |
OCTET_LENGTH | Returns the size (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 one. |
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 an angle expressed in degrees into radians. |
RANDOM/ Versions 22 and laterRAND New in version 22RAND RAND | Returns a random number included between 0.0 and 1.0 (inclusive). |
REPEAT | Returns a character string containing the repetition of count times the str string. |
REPLACE | Returns a character string:- by replacing all the occurrences of a word found in a string by another word.
- by replacing all the occurrences of a word found in a string.
|
REVERSE | Returns a string for which the order of characters is the reversed order of the str string. |
RIGHT | Extracts the last characters from the content of an expression. |
RIGHT OUTER JOIN | Performs an outer join. |
ROLLBACK | Cancels a transaction. |
ROLLBACK TRANSACTION | Cancels a transaction. |
ROLLBACK WORK | Cancels a transaction. |
ROUND | Returns the date rounded to 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. |
SET ISOLATION | Defines the isolation level of transactions. |
SHA/SHA1 | Calculates the 1-bit SHA160 check sum of the string, as described in the 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 laterSPACE 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 find out the standard deviation of a series of values representing a sample of data. |
STDDEV_POP | Used to find out the standard deviation of a series of values representing the full set of data. |
STDDEV_SAMP | Used to find out 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 sub-string from a given position. |
SUBSTRING | Extracts a sub-string from a given position. |
SUM | Returns the total sum of all the item values for all the records selected in the file. |
SYSDATE | Returns the date and the current time. |
SYSTEM_USER | Returns the user name for the current connection. |
TO_CHAR | Formats a date or a number. |
TOP | Returns the first n records found in the result of a query. |
TRANSLATE | Returns a character string with all the specified characters replaced by 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 date truncated to the specified format. |
Versions 16 and laterUCASE UCASE | Returns the str string in uppercase characters, according to the current set of characters. |
Versions 16 and laterUNHEX 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 union queries. |
UPDATE | Updates the file records. |
UPPER | Converts the content of an expression into uppercase characters. |
Versions 15 and laterUUID New in version 15UUID UUID | Generates a UUID (Unique Universal Identifier). A UUID is an integer on 128 bits represented in hexadecimal as a character string in the following format: aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee. A UUID is guaranteed as being unique for each call to the function. |
USER_NAME | Returns the user name for the current connection. |
VALUES | Used with the INSERT statement Defines the added values. |
VARIANCE | Returns the variance for a series of values (data sample). |
VAR_POP | Returns the variance for a series of values (full data set). |
VAR_SAMP | Returns the variance for a series of values (data sample). |
WHERE | Used to specify the selection conditions of a query. |
Versions 16 and laterXOR New in version 16XOR XOR | Logical XOR (exclusive OR). |