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 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 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 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 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 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 a value in radians to 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 a 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 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. |
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 laterLAST_INSERT_ID New in version 15LAST_INSERT_ID LAST_INSERT_ID | Returns the last automatic identifier calculated. |
Versions 16 and laterLCASE LCASE | Returns a string in which all uppercase characters have been converted to lowercase. |
Versions 16 and laterLEAST 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 laterMD5 New in version 16MD5 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. |
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/ Versions 22 and laterRAND New in version 22RAND RAND | Returns a random number between 0.0 and 1.0 (inclusive). |
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. |
ROLLBACK | Cancels a transaction. |
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 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 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 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 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 user name 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 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 truncated date in the specified format. |
Versions 16 and laterUCASE UCASE | Returns a string in which all lowercase characters have been converted to uppercase. |
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 to uppercase characters. |
Versions 15 and laterUUID New in version 15UUID 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 user name 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. |
Versions 16 and laterXOR New in version 16XOR XOR | Logical XOR (exclusive OR). |