webCOMAND

cQL Expressions

A cQL Expression can reference a field, compare values, perform math and logic operations, call functions and more.

The COMAND query languages (cPath, cQL, cQuery) share some common syntax to reference fields, compare values and perform operations.

Dot-Notation

Dot notation is used to reference object field values. A chain of fields can be specified to traverse an object field hierarchy. Each object field in the chain is separated by a dot.  Dot notation is used to reference objects through any type of object or collection field, including through embedded, forward-reference and back-reference fields.

For example, the following cQL uses dot-notation to reference the Name field of an object in the State field of an object in the Address field of a Contact object.

SELECT Address.State.Name FROM Contact

Text Strings

A text string is single- or double-quoted text that can be used in an expression to compare a field, variable or other value to text.  For convenience, in some cases (where noted), a string that begins with a letter and only contains letters and numbers does not need to be quoted.  The following character sequences have special meaning in a quoted string.

  • \' - represents a single quote (useful inside single-quoted strings, but also allowed in double-quoted strings)
  • \" - represents a double quote (useful inside double-quoted strings, but also allowed in single-quoted strings)
  • \n - represents a newline character
  • \r - represents a carriage return character
  • \t - represents a tab character
  • \\ - represents a single backslash

Comparison Operators

Comparison operators are not case sensitive.  However, it is common practice to write them in all uppercase.

  • = - Equal
  • IS - Equal
  • != - Not equal
  • NOT - Not equal
  • < - Less than
  • <= - Less than or equal
  • > - Greater than
  • >= - Greater than or equal
  • LIKE - Match a value against a quoted string where % and _ have special meaning.  % means "any string of any length" and _ means "any single character".  \% and \_ are used to represent an actual % or _ character respectively.
  • ILIKE - Similar to LIKE, except performs a case-insensitive and accent-character-insensitive match.
  • RLIKE - Match a value against a quoted regular expression (an Extended Regular Expression to be specific), which is much more powerful than the simple % and _ used by LIKE.  It can be tricky to write a regular expression in quotes within cQL or cPath.  One helpful tip is that you can escape parenthesis by putting them in a square brackets set like:
    WHERE Text RLIKE "[(].*[)]"
  • BETWEEN x AND y - Range comparison.
  • IN(x, y, z) - Set comparison.

For example, the following cPath will get Images in the Gallery folder with a Width greater than or equal to 320 and a Filename that ends with ".png".

/Gallery/[:Images AND Width >= 320 AND Filename ILIKE '%.png']

Logic Operators

Logic operators are not case sensitive.  However, it is common practice to write them in all uppercase.

  • AND - Logical AND
  • OR - Logical OR
  • NOT - Logical NOT

Arithmetic Operators

  • + - Addition
  • - - Subtraction
  • * - Multiplication
  • / - Division
  • % or MOD - Modulo

Bit Operators

  • TODO