Skip to main content
Cartegraph Campus

Basic SQL Syntax Rules

SQL Syntax can be used in Cartegraph for filtering, reporting, validation rules, and in setting default values for Date, Date and Time, Time and Text field types.

  • Date, Date/Time, and Time
    Are enclosed by the # character. #DATE#, #DATE/TIME#, #TIME#
    • Default Value examples:
      • AddDays(#12/12/2014#, 30)
      • AddDays(#12/12/2014 11:30:02#,12)
      • AddHours(#11:30:02#,6)
    • Filtering and Validation examples:
      • (([Installed] is after #10/7/2014#))
      • (([cgLastModified] is before #10/23/2014 3:45:00 PM#))
  • Quantities
    • Default Value
      • A Quantity default value's unit will always default to the fields Default Unit. The user can only specify a numeric value for the default value's amount.
    • Validation
      • Quantities are enclosed by the " character when validating using the entire Quantity.
        Example:([Height] = "5 ft")
    • Filtering
      • A user can filter on the amount and/or the unit portion of a quantity field. Units must be enclosed by the " character.
        Filtering examples:(([Height.amount] > 6) AND ([Height.unit] is equal to "ft"))
  • Text fields
    • Default Values
      • Default values can be entered without being enclosed in "" and the expression parser will add them on for you.
    • Filtering and Validation:
      • When building filtering and validation statements, values should be enclosed in "".
        Example:(([MUTCDCode\LegendColor] is equal to "Black"))
  • Filters are enclosed in ( ) parenthesis. Using parenthesis is recommended, but not required. For example, a filter might look like this:
    • ([City] is not null) – notice the parenthesis on the outside.
  • ( ) forces precedence and are not required. If ( ) are not around expressions, normal mathematical rules of precedence apply.
  • Fields with spaces or special characters (+, -, *, etc.) in the same name, or start with a number must be defined by [ ] to distinguish it from other token types such as numbers and operators.

 

  • Was this article helpful?