Conditions

Conditions are used to determine whether a particular query matches an expected set of properties. Conditions are used when evaluating which hooks to execute (hook conditions). Conditions are a subset/superset of what you can traditionally put in a normal WHERE clause. You can find the list of available condition types here. In addition to simple expressions, you can use boolean logic to build complex conditions.

Conditions are based on standard SQL null evaluation, similar to SQL WHERE clauses. When a function call is indeterminate, Sundeck will typically return NULL for the operation. If NULL is returned as the root of the condition, it is evaluated as false.

Example Conditions

Example 1

Find all SELECT queries where the transactions table is queried but doesn’t have a WHERE clause.

SQL_MATCHES('.*SELECT .* FROM\s+SALES.PUBLIC.TRANSACTIONS.*', 'i')
AND NOT SQL_MATCHES('.*SELECT .* FROM\s+SALES.PUBLIC.TRANSACTIONS\s+WHERE .*', 'i')

Example 2

Find queries where the submission warehouse is DEMO1 and the USER is not SUPERSTAR_ANALYST

WAREHOUSE = "DEMO1" AND USER <> "SUPERSTAR_ANALYST"

Example 3

Find any queries that access the table DB1.S1.TABLEA but doesn’t contain DB2.S2.TABLEB

TABLES CONTAINS DB1.S1.TABLEA AND TABLES NOT CONTAINS DB2.S2.TABLEB

Example 4

Find queries that have a backtick in them

SQL_CONTAINS('`')