PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • Overview
  • Available commands
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
Overview
WINDEV, WEBDEV and WINDEV Mobile allow you to easily run queries in SQL code. These queries can be:
Two runtime modes of SQL queries are available in WINDEV, WEBDEV and WEBDEV Mobile:
  • Running a 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 query.
  • Running a SQL query without checking the SQL code (with the hQueryWithoutCorrection constant): This option is recommended for the queries run 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 type of connection (Oracle, SQL Server, ...). All the SQL commands supported by the accessed database can be used.
Note: 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.
Note: 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_MONTHAdds months to the specified date.
ALL
ANDCombines several selection conditions (both).
ANYUsed in the sub-queries
ASCreates an alias for each item and for each file used.
ASCUsed with the ORDER BY statement
Defines the ascending order.
ASCIIReturns the ASCII code:
  • of a character.
  • of the first character in a string.
Versions 16 and later
ATAN2
New in version 16
ATAN2
ATAN2
Returns the arctangent of X and Y variables.
AVGCalculates 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.
BETWEENSelects the records for which the value of an item belongs to a range of values.
BINReturns the character string representing "integer" in binary.
BOTTOMReturns 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 found in another string from the beginning or from the end.
CASEReturns 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_LENGTHReturns the size (number of characters) of an expression.
CHARACTER_LENGTHReturns the size (number of characters) of an expression.
COALESCEReturns 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.
CONCATConcatenates several strings together.
Versions 21 and later
CONVERT
New in version 21
CONVERT
CONVERT
Converts a character string from a character set to another one.
COUNTReturns the number of records found in a file or in a group of records.
CREATE TABLEDescribes 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 user name 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).
DECODEReturns the value corresponding to the condition.
Versions 15 and later
DEGREES
New in version 15
DEGREES
DEGREES
Converts an angle expressed in radians into degrees.
DELETEDeletes records from a file.
DESCUsed with the ORDER BY statement
Defines the descending order.
DISTINCTUsed 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 an entire division.
DROP TABLEDeletes 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.
EXISTSUsed 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.
FROMUsed with the SELECT statement
Defines the files used.
Used with the DELETE statement
Defines the files used.
FULL OUTER JOINPerforms 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 arguments or more, returns the greatest value.
GROUP BYClassifies a selection of records by group.
HAVINGUsed to specify one or more conditions on groups of records generated by the GROUP BY statement.
HEXReturns the hexadecimal value of an integer (expressed in base 10).
INSelects the records whose values correspond to a 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 JOINPerforms an inner join.
INSERTAdds a record into a data file.
INSTRReturns the position of a character string.
INTOUsed 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_DAYCalculates 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 that was calculated.
Versions 16 and later
LCASE
New in version 16
LCASE
LCASE
Returns the str string with all the character in lowercase, according to the current set of characters.
Versions 16 and later
LEAST
New in version 16
LEAST
LEAST
With two arguments or more, returns the lowest value.
LEFTExtracts the first characters from the content of an expression.
LEFT OUTER JOINPerforms an outer join
LENReturns the size (number of characters) of an expression.
LENGTHReturns the size (number of characters) of an expression.
LIKESelects the records for which the value of an item corresponds to a specified value (with wildcard characters).
LIMITReturns part of the query result.
LOWERConverts the content of an expression into lowercase characters.
LPADReturns 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.
LTRIMReturns a character string:
  • without the space characters on the left.
  • without a list of characters.
MAXReturns the greatest value of an item for all the records selected in the file.
Versions 16 and later
MD5
New in version 16
MD5
MD5
Calculates the MD5 check sum of the string.
MIDExtracts a sub-string from a given position.
MINReturns the lowest value of an item for all the records selected in the file.
MONTHS_BETWEENReturns the number of months between two dates.
NEW_TIMEReturns the date after time zone conversion.
NEXT_DAYReturns the first day of the week following the specified date or day.
NOTReverses the meaning of the logical operator used.
Used in the sub-queries.
NVLReplaces the null values of the column by a substitution value.
OCTReturns the octal value of an integer (expressed in base 10).
OCTET_LENGTHReturns the size (number of characters) of an expression.
ONUsed when creating a join.
ORCombines several selection conditions (one or the other).
ORDER BYSorts the selected records.
Versions 19 and later
OVERLAY
New in version 19
OVERLAY
OVERLAY
Replaces a string in another one.
PATINDEXReturns the position of the first occurrence of a character string.
POSITIONReturns the position of a character string in an expression.
Versions 15 and later
RADIANS
New in version 15
RADIANS
RADIANS
Converts an angle expressed in degrees 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 included between 0.0 and 1.0 (inclusive).
Versions 16 and later
REPEAT
New in version 16
REPEAT
REPEAT
Returns a character string containing the repetition of count times the str string.
REPLACEReturns 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.
Versions 16 and later
REVERSE
New in version 16
REVERSE
REVERSE
Returns a string for which the order of characters is the reversed order of the str string.
RIGHTExtracts the last characters from the content of an expression.
RIGHT OUTER JOINPerforms 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.
ROUNDReturns the date rounded to the specified format.
RPADReturns 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.
RTRIMReturns a character string:
  • without space characters on the right.
  • without a list of characters.
SELECTFind records in one or more files.
SETUsed 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 1-bit SHA160 check sum of the string, as described in the RFC 3174 (Secure Hash Algorithm).
SOMEUsed in the sub-queries
SOUNDEX, SOUNDEX LIKEReturns the phonetic value
SOUNDEX2, SOUNDEX2 LIKEReturns 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 find out 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 find out 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 find out 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.
SUBSTRExtracts a sub-string from a given position.
SUBSTRINGExtracts a sub-string from a given position.
SUMReturns the total sum of all the item values for all the records selected in the file.
SYSDATEReturns the date and the current time.
Versions 22 and later
SYSTEM_USER
New in version 22
SYSTEM_USER
SYSTEM_USER
Returns the user name for the current connection.
Versions 19 and later
TO_CHAR
New in version 19
TO_CHAR
TO_CHAR
Formats a date or a number.
TOPReturns the first n records found in the result of a query.
TRANSLATEReturns a character string with all the specified characters replaced by other characters.
TRIMReturns 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.
TRUNCReturns the date truncated to the specified format.
Versions 16 and later
UCASE
New in version 16
UCASE
UCASE
Returns the str string in uppercase characters, according to the current set of characters.
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.
UNICODEReturns the integer value defined by the Unicode standard of the first character in the specified expression.
UNIONPerforms union queries.
UPDATEUpdates the file records.
UPPERConverts the content of an expression into uppercase characters.
Versions 15 and later
UUID
New in version 15
UUID
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.
Versions 22 and later
USER_NAME
New in version 22
USER_NAME
USER_NAME
Returns the user name for the current connection.
VALUESUsed with the INSERT statement
Defines the added values.
Versions 17 and later
VARIANCE
New in version 17
VARIANCE
VARIANCE
Returns the variance for a series of values (data sample).
Versions 17 and later
VAR_POP
New in version 17
VAR_POP
VAR_POP
Returns the variance for a series of values (full data set).
Versions 17 and later
VAR_SAMP
New in version 17
VAR_SAMP
VAR_SAMP
Returns the variance for a series of values (data sample).
WHEREUsed to specify the selection conditions of a query.
Versions 16 and later
XOR
New in version 16
XOR
XOR
Logical XOR (exclusive OR).
Minimum required version
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment