Esko Logo Back to Esko Support
Choose your language for a machine translation:


Introduction

This article gives some sample SQL queries. This can be a starting point to create your own. Feel free to amend this list.

For quick reference, educate yourself @

There are three places where SQL queries can be used in Automation Engine:

  1. SmartNames: to calculate the value of a Database Query.
  2. Tasks: to retrieve information from a database as XML file or to update a database with information from a workflow;.
  3. Access Point: to check records matching a condition.

If this is all new to you, consult the manual first: Configuring links to External Databases

To try and create your own queries during self-study, you will first need to create a database for your own personal use. I prefer to use a SQL database and SQL Studio Manager Express for managing my test databases. If you're more familiar with Excel, it is possible (with limitations) to use a spreadsheet as database:

SmartNames - Database Query

You can use SQL to calculate the single value of a Database Query SmartName using information stored in a database. This means you must use the SELECT statement.

Retrieving records

Basics

These are the basics of querying a database. The most typical use case is to use SmartNames to make the result of your query variable. See also Creating a Database Query SmartName

If multiple records are matching the criteria, only the first record will be used.
My database contains multiple orders. To indicate the exact order I want to read, I need to add a WHERE clause on a unique identifier. Typically an ID.

Select distinct

For your interest: When multiple records are matching your criteria (or no criteria are provided), you can use "distinct" to select the last instead of the first matching record. I can't provide you with a good use-case where defining a better filter is not the better solution.

The SmartName can contain multiple values, but returns them as a single string separated with the character |. You can use a String Extract SmartName to separate the values if required.

Retrieving multiple records

If you do really need multiple records in one SmartName, it is possible! Use the FOR XML clause. See more info on FOR XML on simple-talk.com.

  

The XML syntax is more useful as a file. It is better to handle this with Interact with Database task. Adding some light formatting however removes the XML syntax and returns you a string that is very usable as SmartName.
For example: A customer has multiple contacts (saved in multiple records), each with an e-mail address. With the FOR XML clause you can easily list the addresses separated with semicolon, and send one e-mail to all of them.

Conclusion: A typical basic Database Query SmartName has an SQL statement structured like this:

Basic SQL query
SELECT *
FROM MyTableName
WHERE (UniqueID = N'123')
Select on multiple criteria

It is not always possible to filter a single record based on one value. Not all use-cases have a unique identifier. This query will return the ColorStrategy for printing press with ID 005 and a substrate containing "coated".

You can add a third, fourth, fifth... and as many filters as you like! See this table that we will use to determine the appropriate ColorStrategy.

Presses
CustomerPressSubstrateColorStrategy
Customer APress 1UncoatedLinear
Customer APress 1CoatedCMYK_034
Customer APress 1FoilFoil_YellowPlus5
Customer APress 2UncoatedLinear
Customer APress 2CoatedCMYK_065_new
Customer BPress 1UncoatedBump65
Customer BPress 1CoatedCMYK_777
Customer BPress 1FoilFoil_YellowMinus5
Customer BPress 2UncoatedBump65
Customer BPress 2CoatedCMYK_065
Other customersPress 1UncoatedCromOffs
Other customersPress 1CoatedISO_coated_v2
Other customersPress 1FoilFoil_Dec13
Other customersPress 2UncoatedCromOffs_corrected
Other customersPress 2CoatedISO_coated_v2

We will create a SmartName to be used in the proofing ticket, based on MIS information in the job. Job category indicates the press, the substrate is available as Job parameters.

We will define three criteria as filter, in order to point to one unique value.

Select on value in a 3D array

Depending on your database, you will not only use SmartNames in the WHERE clause, but use them to identify the field and table as well. Imagine this XLSX file where we have a tabsheet (table) for each customer, listing the different color strategies used per substrate.

Customer A
SubstratePress 1Press 2
Uncoatedlinearlinear
CoatedCMYK_034CMYK_065_new
FoilFoil_YellowPlus5
Customer B
SubstratePress 1Press 2
UncoatedBump65Bump65
CoatedCMYK_777CMYK_065
FoilFoil_YellowMinus5
Other customers
SubstratePress 1Press 2
UncoatedCromOffsCromOffs_corrected
CoatedISO_coated_v2ISO_coated_v2
FoilFoil_Dec13

We will create a SmartName to be used in the proofing ticket, based on MIS information in the job. Job category indicates the press, the substrate is available as Job parameters.

Instead of providing multiple filters, we've indicated the value for 3 axes in a 3D array of information, pointing to one unique value.

Customer specific ColorStrategy

It is not a healthy situation to organize your proofing profiles and strategies based on customer. But it makes a nice example for this article. (smile)

Linking tables

Databases can have many tables, often in correlation. For example, a customer can be linked to many jobs while one job is linked to exactly one customer. To link to other databases we will use INNER JOIN. There are other join types, read about it here: SQL Joins on w3schools.

In this example we will retrieve the phone number of a customer in the table "Customers" within a job context. The "Jobs" table does not contain this phone number. However, it does contains a unique ID of the customer which allows us to go in the table Customers and search there for the phone number.

Inner Join
SELECT     Customers.PhoneNumber
FROM         Jobs INNER JOIN
                      Customers ON Jobs.CustomerID = Customers.CustomerID
WHERE     ([Job ID] = <joborderid/>)

You can make the join manually by defining cross-table filters in the WHERE clause. Note how the example below first declares the different tables as a single letter (P for pemyParticipants) to reduce the amount of text, keeping a better overview on the query.

Manually joining tables
SELECT     P.Firstname, P.Lastname, T.TitleName, C.Name
FROM       pemyParticipants P, pemyTitles T, pemyCountries C
WHERE      P.Title = T.TitleKey AND P.Country = C.ID AND P.Initials = '<OperatorShort/>'

Making calculations

This is rather straight forward. I would not make the formulas too complex though...

Calculating cost
SELECT     VariableCost * LabelsAmount + FixedCost
FROM       Orders
WHERE     (OrderID = <joborderid/>)
Calculating Body Mass Index (BMI)
SELECT     Weight/SQUARE(Height)
FROM       People
WHERE      FirstName='Homer' AND LastName='Simpson' 
Popular operatorDescription
+Addition operator
-Minus operator
*Multiplication operator
/Division operator
%Modulo operator
DIV (v4.1.0)Integer division
-Unary minus. It changes the sign of the argument.

Learn about usage of functions: SQL functions

Popular functionDescription

AVG()

Returns the average value

COUNT()

Returns the number of rows
MAX()Returns the largest value
MIN()Returns the smallest value

SUM()

Returns the sum
ROUND()Rounds a numeric field to the number of decimals specified
NOW()Returns the current system date and time

Functions don't have to be restricted to one record. The example below calculates the average score on a survey question. There is no WHERE clause used to calculate the average of all responses. (All records are in the table)

Calculating average
SELECT  AVG(Q2)
FROM    entries

Combining multiple values as one

When retrieving multiple values from a record, these will be separated with the character | by default. If you intend to combine these values, there is no need to create extra String Extract SmartNames. By adding formatting, you can combine the values to a single string straight away in the SQL statement with or without separation characters.

Calculating age
SELECT   Title + ' ' + FirstName + ' ' + LastName
FROM     People
WHERE    ID=002

 

SmartNames - 32-bit ODBC Query (Obsolete)

32-bit ODBC Query SmartNames are obsolete since Automation Engine 14. You can't create new anymore, but you can still edit them. Our advice is to convert them to Database Query SmartNames when possible.

old Database Query

The SmartName type "32-bit ODBC Query" was called "Database Query" in Suite 12 and before. It was renamed to make way for the new AE Connect Database Query SmartName type.

A typical set-up was done like this:

Interact with Database task

You can use SQL in the "Interact with Database" task to either retrieve information from a database as XML file for further processing in a workflow or to update a database with information from a workflow.

Popular CommandDescription
SELECTextracts data from a database
UPDATEupdates existing records in a database
DELETEdeletes records from a database
INSERT INTOinserts new records into a database

If this task is new to you, read this first:

SELECT

The select queries are very similar to those used in SmartNames. The main differences are:

  • The result will be output in an XML file and not returned as a string.
  • The result can contain multiple records by default (not just the first matching record) .
  • Hence, it is not required to use a WHERE clause on a unique identifier (it's not required to use a filter at all).
SELECT one field

This is the same query as the first SmartName example. The result is an XML file with the OrderName value for all records.

Resulting XML for SELECT one field
<?xml version="1.0" encoding="UTF-8"?>
<rows created="2014-11-17T14:32:39.941+01:00" producer="sqlexec"
   select="SELECT     OrderName&#xa;FROM         Orders" startrec="1">
   <row>
      <c n="OrderName">hopla</c>
   </row>
   <row>
      <c n="OrderName">hopa</c>
   </row>
   <row>
      <c n="OrderName">Beast</c>
   </row>
</rows>
SELECT all fields

When retrieving info from a small database, it does not hurt to have a little extra information in the XML file. It is easy to select all fields using *, instead of listing all desired fields separately.

result SELECT *
<?xml version="1.0" encoding="UTF-8"?>
<rows created="2014-11-17T14:43:17.155+01:00" producer="sqlexec"
   select="SELECT     *&#xa;FROM        Orders" startrec="1">
   <row>
      <c n="OrderID">234</c>
      <c n="OrderName">hopla</c>
      <c n="OrderSubID">001</c>
      <c n="Description">stijn</c>
      <c n="EskoStatus">created</c>
   </row>
   <row>
      <c n="OrderID">656</c>
      <c n="OrderName">hopa</c>
      <c n="OrderSubID">002</c>
      <c n="Description">kaap hoera</c>
      <c n="EskoStatus">created</c>
   </row>
   <row>
      <c n="OrderID">666</c>
      <c n="OrderName">Beast</c>
      <c n="OrderSubID">002</c>
      <c n="Description">kaap zonder hoop</c>
      <c n="EskoStatus">created</c>
   </row>
</rows>
SELECT with filter

Imagine you want only the records matching a certain condition. This example will retrieve the results from a survey. These results are stored in an XLSX database, connected via ODBC. The WHERE clause defined will only select records matching this condition. Hence the complete database is not returned.

There are multiple input files, but the query is executed only once to have one XML file with the results.
The query is specified like this:

The resulting XML file: (click to expand)

SELECT result
<?xml version="1.0" encoding="UTF-8"?>
<rows created="2014-10-23T17:17:32.788+02:00" producer="sqlexec"
   select="SELECT AllowContact, Comment, Company, CusEmail, CusName, End, IP, Language, Q1, Q2, Q3a, Q3b, Q3c, Q4, Start&#xa;FROM [entries$]&#xa;WHERE Start>'2014-09-25 00:00:00' &#xa;ORDER BY Company" startrec="1">
   <row>
      <c n="AllowContact">No</c>
      <c n="Comment">.</c>
      <c n="Company">Super Labels</c>
      <c n="CusEmail">studio2@Superlabels.co.uk</c>
      <c n="CusName">Freddy Mercury</c>
      <c n="End">2014-10-09 11:22:16</c>
      <c n="IP">87.112.16.74</c>
      <c n="Language">English</c>
      <c n="Q1">10</c>
      <c n="Q2">NA</c>
      <c n="Q3a">8</c>
      <c n="Q3b">8</c>
      <c n="Q3c">10</c>
      <c n="Q4">10</c>
      <c n="Start">2014-10-09 10:54:02</c>
   </row>
   <row>
      <c n="AllowContact">Yes</c>
      <c n="Comment">Viele Fragen können aussagekräftig beurteilt werden!</c>
      <c n="Company">Dosen &amp; Co.</c>
      <c n="CusEmail">major.tom@Dosen.de</c>
      <c n="CusName">Tom Major</c>
      <c n="End">2014-09-29 10:52:16</c>
      <c n="IP">193.105.247.14</c>
      <c n="Language">German</c>
      <c n="Q1">6</c>
      <c n="Q2">6</c>
      <c n="Q3a">2</c>
      <c n="Q3b">4</c>
      <c n="Q3c">2</c>
      <c n="Q4">5</c>
      <c n="Start">2014-09-29 10:17:24</c>
   </row>
   <row>
      <c n="AllowContact">No</c>
      <c n="Comment">.</c>
      <c n="Company">Saturn Reproservice</c>
      <c n="CusEmail">repro@Saturn-repro.at</c>
      <c n="CusName">Frank Klein</c>
      <c n="End">2014-09-25 11:37:29</c>
      <c n="IP">213.129.254.90</c>
      <c n="Language">German</c>
      <c n="Q1">10</c>
      <c n="Q2">6</c>
      <c n="Q3a">8</c>
      <c n="Q3b">4</c>
      <c n="Q3c">10</c>
      <c n="Q4">10</c>
      <c n="Start">2014-09-25 11:35:25</c>
   </row>
   <row>
      <c n="AllowContact">No</c>
      <c n="Comment">Echanges fructueux, disponibilité, clarté dans les explications.</c>
      <c n="Company">MegaBrand</c>
      <c n="CusEmail">piaf@megabrand.fr</c>
      <c n="CusName">Edith Piaf</c>
      <c n="End">2014-10-07 12:11:03</c>
      <c n="IP">78.41.190.133</c>
      <c n="Language">French</c>
      <c n="Q1">10</c>
      <c n="Q2">10</c>
      <c n="Q3a">10</c>
      <c n="Q3b">8</c>
      <c n="Q3c">10</c>
      <c n="Q4">10</c>
      <c n="Start">2014-10-07 12:08:02</c>
   </row>
</rows> 

INSERT INTO

The INSERT INTO clause will add a new record to an existing database. Meaning, this cannot be used to update a value. The example below processes a list of XML files, and adds the info in an XLSX database (based on XML SmartNames). Note how the SQL statement will be executed for every input file.

SQL statements other than SELECT do not have an output, as it is Automation Engine that provides something to the database. If you want to continue the workflow when the Interact with Database task is finished, you can configure the outputs to "add an extra output pin where the task's inputs will be when no errors occur".

If you want to be certain that the SQL statement was successful, you need to open and check your database. Or you can perform a SELECT query polling for the information you just added (wink)

Database Access Point

In Automation Engine 14, there is a third place where SQL can be used. A Database Access Point reads, at a regular time interval, information from an external database. The information is written onto one or more XML files. The Database Access Point can also start a workflow.

There already is a nice detailed example Updating a Product Status in an External System via Interact with Database in the manual.

Article information
Applies to

AE Connect

Esko Product

Automation Engine

3rd party software

SQL database

UsecaseInteract with external databases
Created25-Sep-14
Last revised 15-Sep-2021
AuthorPEMY


Contents