Skip to main content

Celonis Product Documentation

Using Vertica SQL syntax

The Vertica SQL syntax allows you to manage and analyze massive volumes of data quickly and reliably. It is used by the Celonis Platform when writing transformation statements.

The following sections provide Vertica SQL recommendations related to Celonis Platform functionality. For a full set of Vertica SQL documentation, see the official docs here: docs.vertica.com.

Using virtual tables

Vertica has virtual tables that contain information about the actual data tables. They should be used in the following way:

  • To show information about all tables:

    SELECT * FROM TABLES;

  • To show information about the data type of a table:

    SELECT column_name, data_type FROM COLUMNS WHERE table_name = '<TABLENAME>';
Date null checks

These checks work differently compared to HANA. The following doesn't work as in Vertica the parameters of the COALESCE function need to have the same data type:

COALESCE(date_column, '') <> ''

Instead, you should use:

date_column IS NOT NULL
Date concatenation

To concatenate date and time (especially relevant for SAP tables) you need to use the following syntax:

  • Normal:

    CAST(date_column AS DATE) + CAST(time_column AS TIME) AS EVENTTIME

  • With days added:

    CAST((date_column + day_column * INTERVAL '1 day') AS DATE) + CAST(time_column AS TIME) AS EVENTTIME
Inserting hard-coded values in a UNION chain

To add hardcoded values into a table within a UNION chain, use this syntax. A FROM statement is not needed.

...
UNION

SELECT
'text1' AS col1
,'text2' AS col2
,'text3' AS col3

UNION
...
UPDATE Statements

Updating doesn't take the table to be updated in the FROM statement, and the join moves from the FROM statement into the WHERE statement. Joins are made by listing the FROM tables, and stating everything else in the WHERE condition.

UPDATE 
    "_CEL_M2C_ACTIVITIES" AS ACT
SET 
    "CHANGED_FIELD_TEXT_EN" = NAME_MAPPING_COLUMNS_EN.PRETTY_NAME,
    "CHANGED_FIELD_TEXT_DE" = NAME_MAPPING_COLUMNS_DE.PRETTY_NAME
FROM "M2C_NAME_MAPPING_COLUMNS" AS NAME_MAPPING_COLUMNS_EN
        ,"M2C_NAME_MAPPING_COLUMNS" AS NAME_MAPPING_COLUMNS_DE

WHERE
        ACT."CHANGED_TABLE" = NAME_MAPPING_COLUMNS_EN."TABLE_NAME" AND
        ACT."CHANGED_FIELD" = NAME_MAPPING_COLUMNS_EN."FIELD_NAME" AND
        NAME_MAPPING_COLUMNS_EN."LANGUAGE" = 'E' AND
        ACT."CHANGED_TABLE" = NAME_MAPPING_COLUMNS_DE."TABLE_NAME" AND
        ACT."CHANGED_FIELD" = NAME_MAPPING_COLUMNS_DE."FIELD_NAME" AND
        NAME_MAPPING_COLUMNS_DE."LANGUAGE" = 'D'
;
Insert multiple rows at once

Inserting multiple rows at once works differently in Vertica. Here's how to do it:

INSERT INTO table_name
SELECT value1,value2
UNION SELECT value1,value2
Vertica SQL troubleshooting and further information

The following troubleshooting topics and further information are available: