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. |
Please log in or register to submit a comment.