Danger Will Robinson
Advanced filter criteria should be avoided under most circumstances.
However, should you need to use them, here are the guidelines:
- Only available for data sources using the legacy survey system (e.g. not available on AI Analytics Datasets, Case Management and other data sources)
- In most cases you need to use the letter-prefixing for any columns you wish to reference. For example, instead of “ENTY_ID > 0”, you need to specify “A.ENTY_ID > 0”.
- The “A.” prefix is always the survey base table (SDATA_SURVEYID).
- Other letter prefixes (B, C, D) may be present in some situations based on use case. There is no way to know definitively what those prefixes are without trial-and-error. For example, if you are referencing a variable in a loop, in most cases, it will be B. (e.g. “B.Q1 > 2”) but this is not guaranteed in all situations.
- Letter prefixes are sequential, so A., B., C., D. etc and generally there are fewer than 5
- You can use any of the standard SQL WHERE clause syntax supported by the back-end database EXCEPT you may not use JOINs
- If you use an IN or NOT IN with a SELECT list … you should be ashamed. Those perform horribly, particularly on large datasets.
- Your criteria may NOT contain references to system tables or views and may not contain the text “–“
- Your criteria may NOT contain some less common mathematical operators such as modulus (%) and exponentiation (^) and bit-wise operators (& and |)