The SQL WHERE clause is used to filter records based on specified conditions.
Below are the common operators and functions supported in the WHERE clause in DocuWare workflows:
Standard SQL Operators
Comparison Operators
= (equal to)
!= or <> (not equal to)
> (greater than)
< (less than)
>= (greater than or equal to)
<= (less than or equal to)
Logical Operators
AND (both conditions must be true)
OR (either condition can be true)
NOT (negates the condition)
Range Operator
BETWEEN (within a range, inclusive)
Set Operator
IN (matches any value in a list)
Pattern Matching Operators
LIKE (matches a pattern using wildcards)
% represents zero or more characters
_ represents a single character
Null Operators
IS NULL (is a null value)
IS NOT NULL (is not a null value)
Standard SQL Functions
String Functions
Function | Description |
|---|---|
CONCAT(string1, string2, ...) | Concatenates two or more strings |
LEN(string) (MSSQL) LENGTH(string) (MySQL) | Returns the length of a string |
UPPER(string) | Converts a string to uppercase |
LOWER(string) | Converts a string to lowercase |
TRIM([characters FROM] string_expression) | Removes leading, trailing, or both leading and trailing characters (default is spaces) from a string |
REPLACE(string, old_substring, new_substring) | Replaces occurrences of a substring within a string with another substring |
SUBSTRING(string, position, length) | Extracts a substring that starts at a specified position with a given length |
POSITION(substring IN string) (MySql only) | Returns the position of the first occurrence of a substring within a string |
CHAR_LENGTH(string) (MySql only) | Returns the length of a string in characters |
RTRIM(string) | Removes trailing spaces from a string |
LTRIM(string) | Removes leading spaces from a string |
Numeric Functions
Function | Description |
|---|---|
ABS(number) | Returns the absolute value of a number |
ROUND(number, decimals) | Rounds a number to a specified number of decimal places |
CEIL(number) or CEILING(number) | Rounds a number up to the nearest integer |
FLOOR(number) | Rounds a number down to the nearest integer |
%(number, divisor) (MSSQL) MOD(number, divisor) (MySQL) | Returns the remainder of a division |
POWER(number, exponent) | Returns a number raised to a specified power |
SQRT(number) | Returns the square root of a number |
Date and Time Functions
Function | Description |
|---|---|
GETDATE() (MSSQL) | Returns the current date |
CURRENT_TIME() (MySql only) | Returns the current time |
CURRENT_TIMESTAMP() | Returns the current date and time |
DATEPART(part FROM date) (MSSQL) EXTRACT(part FROM date) (MySQL) | Extracts a part (e.g., year, month, day) from a date |
DATEADD(datepart, number, date) (MSSQL) ADDDATE(date, INTERVAL value addunit) (MySQL) | Adds or subtracts a specified interval to a date |
DATEDIFF(datepart, date1, date2) (MSSQL) DATEDIFF(date1, date2) (MySQL) | Returns the difference in days between two dates |
FORMAT(date, format) (MSSQL) DATE_FORMAT(date, format) (MySQL) | Formats a date according to a specified format |
Aggregate Functions
An aggregate function is a function that performs a calculation on a set of values, and returns a single value.
Function | Description |
|---|---|
COUNT(expression) | Returns the number of rows that match a specified condition |
SUM(expression) | Returns the sum of a numeric column |
AVG(expression) | Returns the average value of a numeric column |
MIN(expression) | Returns the minimum value in a set of values |
MAX(expression) | Returns the maximum value in a set of values |
GROUP_CONCAT(expression) | Concatenates values from a group into a single string (MySQL specific) |
Conditional Functions
Function | Description |
|---|---|
CASE WHEN condition THEN result [WHEN ...] ELSE result END | Returns a value based on specified conditions |
COALESCE(expression1, expression2, ...) | Returns the first non-null value in a list |
NULLIF(expression1, expression2) | Returns null if the two expressions are equal |
Conversion Functions
Function | Description |
|---|---|
CAST(expression AS data_type) | Converts an expression from one data type to another |
CONVERT(expression, data_type) | Converts an expression from one data type to another |
Subqueries
Subqueries, also known as inner queries or nested queries, are supported in the WHERE clause in basic formats. A subquery is a query within another query and can be used to provide more complex filtering criteria. Subqueries in the WHERE clause can be powerful tools for filtering data based on complex conditions.
Single-row Subquery
Find employees who work in the same department as 'John Doe'.
WHERE department_id = ( SELECT department_id FROM employees WHERE employee_name = 'John Doe' );Multi-row Subquery
Find employees who work in departments located in 'New York'.
WHERE department_id IN ( SELECT department_id FROM departments WHERE location = 'New York' );Correlated Subqueries (not supported)
Correlated subqueries, which reference columns from the outer query and are evaluated once for each row processed by the outer query, are not supported. As a result, these constructs are not tested and cannot be guaranteed to work consistently.
Joins and Apply in Subqueries (not supported)
JOIN and APPLY operators can be used in subqueries to reference other tables. However, it is important to note that this is not supported. As a result, these constructs are not tested and cannot be guaranteed to work consistently.
WHERE DW_DWDOCID IN (SELECT d.DWDOCID FROM [Documents] as d INNER JOIN [DOCUMENT_POOL] as dp ON d.DWDOCID = dp.DWDOCID WHERE d.DWDOCID < 20)