Writing queries

There are many functions that accept a query as argument, such as queryContacts, queryContents, executeSimpleQuery, executeUpdate.
All of these functions use the MQL language in order to express data access commands. This language is a sub dialect of standard SQL language, with some extensions and some limitations.

Basic syntax

Basic syntax is: SELECT * FROM TABLENAME WHERE CONDITIONS ORDER BY ORDERBYEXPRESSIONS

Some examples:

 SELECT * FROM CONTACTS WHERE status='subscribed'

 SELECT * FROM CONTACTS WHERE name = ?

 SELECT * FROM CONTACTS WHERE relationfield in (SELECT myvalue FROM MYCUSTOMTABLE WHERE customfield=123)

 SELECT name,myfield FROM MYCUSTOMTABLE ORDER BY myorderfield

 SELECT name,myfield FROM MYCUSTOMTABLE INNER JOIN OTHERTABLE ON MYCUSTOMTABLE.a=OTHERTABLE.b ORDER BY myorderfield

 SELECT * FROM MYCUSTOMTABLE WHERE myfield in (SELECT idcontact from CONTACTS)

 SELECT * FROM MYCUSTOMTABLE WHERE myfield = strtonumber('100,66')

 SELECT max(myfield) FROM MYCUSTOMTABLE

 SELECT * FROM CONTACTS WHERE to_datetime(data_ultima_modifica) >= now(-3600)

 SELECT * FROM CONTACTS WHERE to_date(data_iscrizione) >= today(-2)

 SELECT * FROM CONTACTS WHERE to_date(data_iscrizione) >= strtodate('10/01/2020')

 SELECT * FROM CONTACTS WHERE to_datetime(data_iscrizione) >= strtodatetime('10/01/2020 00:05:00')

 SELECT email, data_eliminazione FROM contacts WHERE status='unsubscribed' AND to_date(data_eliminazione) = strtodate('05/08/2020 19:22:00')

 SELECT * FROM WEBPAGES WHERE name  LIKE 'mypagenamestartswith%'

 SELECT * FROM CONTACTS WHERE to_date(data_ultima_modifica) >= to_date(dateadd(data_ultima_modifica, -1, 'day'))

 SELECT * FROM CONTACTS WHERE STATS.TOTALCLICKS > 10

 SELECT * FROM CONTACTS WHERE STATS.IDCHANNEL = 0 AND STATS.TOTALCLICKS > 10

Supported SQL functions

This is the actual set of SQL functions:

• count(*): same as SQL function
• max(columnname): same as SQL function
• min(columnname): same as SQL function
• avg(columnname): same as SQL function
• sum(columnname): same as SQL function
• lcase(columnname): same as SQL function (convert string to lowercase)
• ucase(columnname): same as SQL function (convert string to uppercase)
• to_date(columnname): converts a column/scalar value to a date without time information
• to_datetime(columnname): converts a column/scalar value to a date with time information
• to_int (columnname): converts a value (string, decimal...) in an integer
• to_string (columnname): converts a value (date, decimal...) into a string value
• strtodate(date_literal): converts a string written as the date format of the user which is issuing the query (using the user timezone)
• strtodatetime(datetime_literal): converts a string written as the datetime format of the user which is issuing the query (using the user timezone)
• strtonumber (number_literal): converts a string containing numeric characters in a number
• now(offset_in_millis): obtains the current date time, scaled by an offset in milliseconds
• today(offset_in_day): obtains the current date without time (at midnight), scaled by an offset in days
• year(columnname): same as SQL function
• dayofmonth(columnname): same as SQL function
• weekday(columnname): same as SQL function
• month(columnname): same as SQL function
• dateadd(columnname,number_of_days,'day'): adds a number of days to the specified date
• fielddescription(fieldname): performs the lookup of a value and returns the value description of a list of value field

Dealing with dates and datetimes

When you write datetime and date literals directly in your MQL query, pay attention to always use the strtodate/strtodatetime functions which guarantee you that the system is going to parse them using your datetime format and especially your timezone.

Joins and Subqueries

You can use INNER JOIN, RIGHT JOIN and LEFT JOINs while writing queries which are on a custom table but not on system tables.
You can always use the 'IN' operator with a subselect in order to obtain the same result.
In subselects you cannot use data from the outer query.

System tables supported

Actually, you can write queries on all your custom tables and on some of the system tables.

You can only issue MQL queries in order to read data from system tables and you have to use the specific functions (like mergeContact) to update data.

Supported system tables:

• CONTACTS: represents your contacts database;
  
  You must have permissions to export data from the database to perform queries on this table.
• GROUPS: represents the set of contacts 'audiences';
• STATICGROUPMEMBERS: represents the members of a static audience;
  
  You must have permissions to export data from the database to perform queries on this table.
• CONTENTS: represents your contents database;
• NEWSLETTERS: represents your communications;
• WEBPAGES: represents your webpages;
• SURVEYS: represents your survey;
• SURVEYSESSIONS: represents the sessions of one survey;
  
  You must have permissions to export data from the database to perform queries on this table.
• WORKFLOWS: represents all the automated flows in every state (active, paused or stopped);
• WORKFLOWSESSIONS: represents the active sessions of the automated flow;
• WORKFLOWSESSIONSFINISHED: represents the ended sessions of the automated flow;
  
  You must have permissions to export data from the database to perform queries on this table.
• SIMPLEMESSAGES: represents transactional messages (sent or bounced);
  
  You must have permissions to export data from the database to perform queries on this table.
• RECEIVEDMESSAGES: represents the SMS and EMAIL messages received using the Message Receiver service;
  
  You must have permissions to export data from the database to perform queries on this table.
• RECEIVEDMESSAGESFOLDERS: represents the set of folders for messages received using the Message Receiver service;
• CONTENTTAGSTATS: contact statistics related with content tags;
• NEWSLETTERTAGSTATS: contact statistics related with communication tags.
• CONVERSIONEVENTS: represents the conversion events.

System tables: CONTACTS

The system tables contacts represent all of your contacts.
There is a column for each "field", such as 'email', 'status', 'timezone'....
Special columns:

System tables: GROUPS

Represents all audiences of contacts (dynamic and static)

Columns:

System tables: STATICGROUPMEMBERS

Represents all the members of static audiences of contacts

Columns:

System tables: CONTENTS

Represents all the contents

Columns:

System tables: NEWSLETTERS

Represents all the communications

Columns:

System tables: WEBPAGES

Represents all the webpages

Columns:

System tables: SURVEYS

Represents all the surveys

Columns:

System tables: SURVEYSESSIONS

Represents all the sessions of a survey

Columns:

System tables: WORKFLOWS

Represents all the existing automated flows.

Columns:

System tables: WORKFLOWSESSIONS

Represents all the active sessions of all existing automated flows.

Columns:

System tables: WORKFLOWSESSIONSFINISHED

Represents all the ended sessions of all existing automated flows.

Columns:

System tables: SIMPLEMESSAGES

Represents all the transactional messages which have been processed

Columns:

SELECT idsimplemessage from SIMPLEMESSAGES where status = 'notsent' or status='error'

System tables: RECEIVEDMESSAGES

Represents all the received messages

Columns:

System tables: RECEIVEDMESSAGESFOLDERS

Represents all the received messages

Columns:

System tables: CONTENTTAGSTATS

Contact statistics related with content tags

Columns:

System tables: NEWSLETTERTAGSTATS

Contact statistics related with communication tags

Columns:

System tables: CONVERSIONEVENTS

Represents the conversion events

Columns: