Skip to main content

Celonis Product Documentation

Vertica SQL
Exploring information about tables and columns

Vertica has virtual tables that contain information about the actual data tables. You can use them in the following way:

SELECT * FROM TABLES; -- shows you information about all tables
SELECT column_name, data_type FROM COLUMNS WHERE table_name = '<TABLENAME>'; -- shows you the data types of the table <TABLENAME>
Differences in Vertica SQL compared to other SQL dialects
Date null checks

They work differently compared to e.g. HANA:

COALESCE(date_column, '') <> '' -- does not work because in Vertica the parameters of the COALESCE function need to have the same data type

date_column IS NOT NULL -- this works
Date concatenation

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

-- Generic
CAST(date_column AS DATE) + CAST(time_column AS TIME) AS EVENTTIME -- normal
CAST((date_column + day_column * INTERVAL '1 day') AS DATE) + CAST(time_column AS TIME) AS EVENTTIME -- with days added

-- Examples
CAST(B.BLDAT AS DATE) + CAST('00:00:00' AS TIME) AS EVENTTIME -- normal
CAST((B."ZFBDT" + B."ZBD1T" * INTERVAL '1 day') AS DATE) + CAST('23:59:59' AS TIME) AS EVENTTIME -- with days added
Inserting hard-coded values in a UNION chain

If you would like to add hardcoded values into a table within a UNION chain you can use the syntax

...
UNION

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

UNION
...

A FROM statement is not needed.

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 done by listing the from tables and stating everyting else in the where condition

Update Statement

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. Check the codeblock below to see how this can be done

Multiple Row Insert

-- This does not work in Vertica
INSERT INTO table_name(column1,column2)
VALUES (value1,value2),
       (value1,value2)

-- Instead, use the following syntax
INSERT INTO table_name
SELECT value1,value2
UNION SELECT value1,value2