Supported Expression Types

The following expression types are explicitly supported in this release of the 5800 system:

• Make use of JDBC escape syntax wherever possible. The escape syntax makes the query syntax more portable without losing functionality.

• Comparison operations: expr1 OP expr2, where OP is one of =, !=, <>, <=, >=, <, or >.

  The comparison operations can compare any two expressions (for example, two attribute values or one attribute value and one literal value). The two values must be of compatible types. For example, a < comparison cannot be used between a numeric literal value and a string-valued attribute. Note that <> and != are synonyms.

• The concatenation operator, expr1 || expr2.

  The concatenation operator || is an SQL standard way of concatenating two expressions to produce a combined output string.

• Use of parentheses to indicate precedence of evaluation.

• Boolean operators AND, OR, and NOT.

• expr1 [NOT] LIKE expr2.

  • Attempts to match a character expression to a character pattern, which is a character string that includes one or more wildcards.

  • % matches any number (zero or more) of characters in the corresponding position in expr1.

  • _ matches one character in the corresponding position in expr1.

  • Any other character matches only that character in the corresponding position in the character expression.

• expr1 BETWEEN expr2 AND expr3

• expr [NOT] IN (valueslist).

  ---

  Note –

  The 5800 system emulator supports (but the cluster database does not) a JDBC “escape” clause that allows you to treat either % or _ as constant characters. There is currently no way to accomplish this in a LIKE clause in a query on a live cluster.

  ---

• The following JDBC function escapes have been tested and are supported:

  • {fn UCASE(string)} to convert a string to uppercase.

  • {fn LCASE(string)} to convert a string to lowercase.

  • {fn ABS(value)} to take the absolute value of a numeric expression.

  • {fn LENGTH(string)} to get the length of a string.

  • {fn SUBSTRING(string, start, length)} to get a character string formed by extracting length characters from string beginning at start.

  • {fn LOCATE(string1, string2[, start])} to locate the position in string2 of the first occurrence of string1, searching from the beginning of string2. If start is specified, the search begins from position start. 0 is returned if string2 does not contain string1.

  • {fn LTRIM(string)} to remove the leading blank spaces in a character string

  • {fn RTRIM(string)} to remove the trailing blank spaces of a character string

  • {fn CONCAT(string1, string2)} to get the concatanated character string by appending string2 to string1. If a string is NULL, the result is DBMS-dependent.

  • {fn TIMESTAMPDIFF(interval, timestamp1, timestamp2)}. An integer representing the number of interval by which timestamp2 is greater than timestamp1. interval may be one of the following: SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_MONTH, SQL_TSI_QUARTER, or SQL_TSI_YEAR.

  • {fn TIMESTAMPADD(interval, count, timestamp)}. A timestamp calculated by adding count number of intervals to timestamp. interval may be one of the following: SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, or SQL_TSI_YEAR.

• field_name IS NOT NULL to return all objects that have a value for a specific field. In particular, the query "system.object_id IS NOT NULL" can be used to query all the objects in the system.

  ---

  Note –

  The form "field_name IS NULL" to identify all the objects that lack a value for a specific field is not supported and will not work properly in all cases.

  ---