ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / Editors / Query editor / SQL
  • Overview
  • Available commands
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
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 WINDEV 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_MONTHSAdds months to the specified date.
ALL
ANDCombines several selection conditions (both).
ANYUsed in the sub-queries
ASCreates an alias for each item and file used.
ASCUsed with the ORDER BY clause
Defines the ascending order.
ASCIIReturns the ASCII code:
  • of a character.
  • of first character found in a string.
ATAN2Returns the arctangent of X and Y variables.
AVGCalculates the mean for a set of values.
BEGINStarts a transaction.
BEGIN WORKStarts a transaction.
BEGIN TRANSACTIONStarts a transaction.
BETWEENSelects the records for which the value of an item belongs to a range of values.
BINReturns a binary representation of a number, as a string value.
BITANDPerforms a bitwise AND operation on two numeric expressions.
BITANDNOTPerforms bitwise AND and NOT operations on two numeric expressions.
BITNOTPerforms a bitwise negation on a numeric expression.
BITORPerforms a bitwise OR operation on two numeric expressions.
BITXORPerforms a bitwise XOR operation on two numeric expressions.
BOTTOMReturns the last n records found in the result of a query.
BTRIMDeletes a string within another string from the beginning or from the end.
CASEReturns the value corresponding to the condition.
CASTUsed to convert a number into another type.
CBRTReturns the cube root of a number.
CEILING
CEIL
Returns the smallest integer value that is larger than or equal to a number.
CHAR_LENGTHReturns the length (number of characters) of an expression.
CHARACTER_LENGTHReturns the length (number of characters) of an expression.
COALESCEReturns the first not-null expression among its arguments.
COMMITValidates a transaction.
COMMIT TRANSACTIONValidates a transaction.
COMMIT WORKValidates a transaction.
CONCATConcatenates multiple strings.
CONVERTConverts a character string from a character set to another one.
COUNTReturns the number of records in a file or in a group of records.
CREATE TABLEDescribes and creates a file/table.
CURRENT_TIMESTAMPReturns the date and the local time of server.
CURRENT_USERReturns the username for the current connection.
DATEADDAdds a value to the start date and returns the new date.
DATEDIFFCalculates the difference between two dates in a given unit. The return value is a signed integer.
DATEDIFFBIGCalculates the difference between two dates in a given unit. The return value is a signed big integer.
DATEFROMPARTSReturns a date from the specified parts.
DATETIMEFROMPARTSReturns a datetime from the specified parts.
DATEPARTReturns an integer that corresponds to a specified part of a given datetime.
DAYReturns the day of the month, i.e. a number between 1 and 31.
DAYOFMONTHReturns the day of the month (between 1 and 31).
DAYOFWEEKReturns the day in the week (1 for Sunday, 2 for Monday, etc.).
DAYOFYEARReturns the day of the year (between 1 and 366).
DECODEReturns the value corresponding to the condition.
DEGREESConverts a value in radians to degrees.
DELETEDeletes records from a file.
DESCUsed with the ORDER BY clause
Defines the descending order.
DISTINCTUsed with the SELECT statement
Deletes the duplicated records (duplicates) from the result of the query.
DIVPerforms a division.
DROP TABLEDeletes a file/table physically.
ELTReturns the nth string of a list of strings.
EOMONTHxx
EVERYReturns True if all the arguments are checked and true.
EXISTSUsed in the sub-queries
FIELDReturns the index of the string that must be found in the list.
FIND_IN_SETReturns 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.
GETDATEReturns the date and the local time of server.
GETUTCDATEReturns the date and the UTC time of server.
GREATESTWith two or more arguments, returns the greatest value.
GROUP BYClassifies a selection of records by group.
GROUP_CONCATConcatenates the non-null values of an item from a series of records into a string.
HAVINGUsed to specify one or more conditions on groups of records generated by the GROUP BY statement.
HEXReturns the hexadecimal value of an integer number (expressed in base 10).
INSelects the records whose values correspond to a specified list of values.
Used in the sub-queries.
INITCAPReturns a string containing the first letter of each word in uppercase characters.
INNER JOINPerforms an inner join.
INSERTAdds a record to a data file.
INSTRReturns the position of a character string.
INTERSECTAllows you to combine selection queries (SELECT statement).
INTOUsed with the INSERT statement
Defines the file used.
ISDATEAllows you to determine if an expression corresponds to a date.
IS JSONAllows 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_OBJECTRetrieves a JSON object from any item.
JSON_OBJECTAGGReturns a JSON object containing key-value pairs for each specific key and value in a set of SQL values.
JSON_ARRAYRetrieves a JSON array from any item. The JSON content retrieved corresponds to an array.
JSON_ARRAYAGGReturns a JSON array containing key-value pairs for each specific key and value in a set of SQL values.
JSON_EXISTSRetrieves records with a JSON item containing data.
JSON_QUERYRetrieves a JSON object or array.
JSON_VALUERetrieves the value of an element contained in the JSON item.
LAST_DAYCalculates the date of last day for the specified month.
LAST_INSERT_IDReturns the last automatic identifier calculated.
LCASEReturns a string in which all uppercase characters have been converted to lowercase.
LEASTWith two or more arguments, returns the lowest value.
LEFTExtracts the first characters from the content of an expression.
LEFT OUTER JOINPerforms an outer join
LENReturns the length (number of characters) of an expression.
LENGTHReturns the length (number of characters) of an expression.
LIKESelects the records for which the value of an item matches a specified value (with wildcard characters).
LIMITReturns part of the query result.
LOWERConverts the content of an expression to lowercase characters.
LPADReturns 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.
LTRIMReturns a character string:
  • without space characters on the left.
  • without a list of characters.
MATCH AGAINSTUsed to get the relevance index of the record during a full-text search.
MAXReturns the greatest value of an item for all selected records in the file.
MD5Calculates the MD5 checksum of the string.
MIDExtracts a substring from a given position.
MINReturns the lowest value of an item for all selected records in the file.
MONTHReturns the month of a date.
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 based on 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 number (expressed in base 10).
OCTET_LENGTHReturns the length (number of characters) of an expression.
ONUsed when creating a join.
ORCombines several selection conditions (one or the other).
ORDER BYSorts the selected records.
OVERLAYReplaces a string in another string.
PATINDEXReturns the position of the first occurrence of a character string.
POSITIONReturns the position of a character string in an expression.
RADIANSConverts a degree value into radians.
RANDOM/RANDReturns a random number between 0.0 and 1.0 (inclusive).
REGEXPEvaluates a regular expression inside an SQL query.
REPEATReturns a string containing a string that is repeated a specified number of times.
REPLACEReturns 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.
REVERSEReturns a string in which the character order of a specified string is reversed.
RIGHTExtracts the last characters from the content of an expression.
RIGHT OUTER JOINPerforms an outer join.
REGEXP_LIKEEvaluates a regular expression inside an SQL query.
ROLLBACKCancels a transaction.
ROLLBACK TO SAVEPOINTRolls back all actions performed within the transaction since the savepoint was established.
ROLLBACK TRANSACTIONCancels a transaction.
ROLLBACK WORKCancels a transaction.
ROUNDReturns the rounded date in the specified format.
RPADReturns 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.
RTRIMReturns a character string:
  • without space characters on the right.
  • without a list of characters.
SAVEPOINTCreates a savepoint in the current transaction.
SELECTFind records in one or more files.
SETUsed with the UPDATE statement
Defines the name of the items used.
SET ISOLATIONDefines the isolation level of transactions.
SHA/SHA1Calculates the 160-bit SHA1 checksum of the string, as described in RFC 3174 (Secure Hash Algorithm).
SHIFT_LEFT_OPBitwise operator: <<
SHIFT_RIGHT_OPBitwise operator: >>
SOMEUsed in the sub-queries
SOUNDEX, SOUNDEX LIKEReturns the phonetic value
SOUNDEX2, SOUNDEX2 LIKEReturns the phonetic value (adapted to French).
SPACEReturns a string containing N spaces.
SPLIT_PARTDivides the string according to a separator and returns the nth part.
START TRANSACTIONStarts a transaction.
STDDEVUsed to get the standard deviation of a series of values representing a sample of data.
STDDEV_POPUsed to get the standard deviation of a series of values representing the full set of data.
STDDEV_SAMPUsed to get the standard deviation of a series of values representing a sample of data.
STRING_AGGUsed to concatenate non-null strings from a list of values.
SUBSTRExtracts a substring from a given position.
SUBSTRINGExtracts a substring from a given position.
SUMReturns the total sum of the values of an item for all selected records in the file.
SYSDATEReturns the current date and time.
SYSTEM_USERReturns the username for the current connection.
TO_CHARFormats a date or a number.
TOPReturns only the first n records of a query result.
TRANSLATEReturns a character string with all the specified characters replaced with 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 truncated date in the specified format.
UCASEReturns a string in which all lowercase characters have been converted to uppercase.
UNHEXContrary 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 combining queries.
UPDATEUpdates the file records.
UPPERConverts the content of an expression to uppercase characters.
UUIDGenerates 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_NAMEReturns the username for the current connection.
VALUESUsed with the INSERT statement
Defines the added values.
VARIANCEReturns the variance of a series of values (data sampling).
VAR_POPReturns the variance of a series of values (full data set).
VAR_SAMPReturns the variance of a series of values (data sampling).
WHEREUsed to specify the selection conditions of a query.
XORLogical XOR (exclusive OR).
YEARReturns the year of a date.
Minimum version required
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 07/06/2023

Send a report | Local help