The following functions and operators are available in the DB Query Builder.
Functions:
- .AND. - For the condition to be evaluated as true, the logical expression on both sides of the AND must be true.
- .OR. - For the condition to be evaluated as true, either the logical expression on one side or the other side of the OR must be true.
Note: if .AND. and .OR. are used in the same expression, the .OR. function is generally evaluated first. Please check that the function operates as expected.
- .NOT. Logical NOT - returns false if the expression is true and returns true if the expression is false.
- ( ) Parentheses - for grouping logical InfoWater Pro expressions. InfoWater Pro evaluates expressions in the inner-most parentheses first, then moves to the outer-most parentheses.
- + Addition
- - Subtraction
- ' Quote - For containing character strings or values in a character field. For example 'CAST IRON' or '10' (assuming '10' was stored in a character field).
- SUBSTR( ) - Returns a substring derived from a specified character field. For example SUBSTR('CAST IRON',3,2) returns 'ST'. SUBSTR('CAST IRON',3) returns all characters from the third position to the end of the string; 'ST IRON'.
- UPPER( ) - Converts all lowercase characters in a string to uppercase. For example UPPER('Main Street') returns 'MAIN STREET'.
- CTOD( ) - Converts a character string to a date object. For example CTOD('2/15/98') returns 02/15/98. CTOD('') returns / / .
- DTOC( ) - Converts a date expression to a character string. For example DTOC(02/15/98) returns '2/15/98'.
- DTOS( ) - Converts a date expression to a character string in YYYYMMDD format. For example, DTOS(02/15/98) returns '19980215'.
- DATE( ) - Returns your computer's current system date. To evaluate the current date as a string, type the following: STR(DATE( )).
- DAY( ) - Returns the numeric value of the day of the month in a date expression. For example DAY({2/15/98}) returns 15.
- MONTH( ) - Returns the numeric value of the month in a date expression. For example MONTH({2/15/98}) returns 2.
- YEAR( ) - Returns the year of a specified date expression as a four-digit number. For example YEAR({2/15/98}) returns 1998.
- LEFT( ) - Returns a specified number of characters from the beginning of a character string. For example, LEFT('CAST IRON',4) returns 'CAST'.
- VAL( ) - Returns a specified character string as a numeric value. For example, VAL('1200 Main Street') returns 1200.
- STR( ) - Returns the character string equivalent of a specified number. For example STR(-32) returns '-32' and STR(100) returns '100'.
Operators:
- = Equal - Operand-1 is equal to Operand-2
- <> Not Equal - Operand-1 is not equal to Operand-2
- => Greater Than or Equal - Operand-1 is greater than or equal to Operand-2
- <= Less Than or Equal - Operand-1 is less than or equal to Operand-2
- > Greater Than - Operand-1 is greater than Operand-2
- < Less Than - Operand-1 is less than Operand-2
- <> Not Contain - Operand-1 does not contain Operand-2
- $ Is Contained In - Operand-1 is contained in Operand-2