General expression format

DPQL Reference

Most clauses of a DPQL statement accept a general expression format. You can use expressions to carry out more complicated queries.

Complex expressions are made up of smaller, simpler expressions. Expressions are made up of the following components:

Type Example Details
Numbers 5, 37.4 Simple, literal referencesto integer or decimal values
Strings ‘string’ or “string” Strings are literal references to text.These will commonly be used intests/comparisons and aliases.
Null tickets.labels.label<> NULL You can use this in tests to checkwhether a certain value exists. If acertain value has been set, it will benot equal (<>) to NULL.
Parentheses charge_time / (60 * 60) Parentheses are used to enforce theorder in which the expression is evaluated.
Column references table.col[.col2...] Column references directly retrieve datafrom your helpdesk. tickets.subject andtickets.agent.name are both valid columnreferences.You can use multiple column partsto combine data from different tables, basedon a shared field between them (the equivalentof an SQL join). For example,tickets.person.organization.name which wouldretrieve the name of the organization the personthat started the ticket belongs to.
Function calls DPQL_COUNT(), DPQL_CURDATE(),DPQL_MATRIX(group X, group y) These are useful functions, for example doingcalculations on the inputs (arguments) you givethem, changing formatting, or retrievinginformation. COUNT() is a function.Some functions don’t need any input e.g.CURDATE() just gets the current date.
Comparisons (expression) [=, <>, >=, >, <, <=] (expression) Enable you to compare two expressionsIf true, the value = 1. False = 0.To do not equals
Placeholders %TODAY%,%LAST_YEAR% Placeholders are dynamic elements of aDPQL query that are automatically updatedto the appropriate value when the query is run.They are useful in comparisons e.g. you canuse tickets.created_date = %PAST_7_DAYS%to match tickets created in the last 7 days.Note that the value is for the timezoneset in the agent’s preferences.e.g. %TODAY%
Logic AND, OR Used to logically combine two expressions.

Published: 22/05/2018

Last updated: 22/05/2018