Skip to main content
Question

About Vertica SQL in transformation stage

  • January 29, 2020
  • 3 replies
  • 12 views
.
Level 2

Hi,

According to the data engineering track, the Celonis transformation uses Vertica SQL. I searched online and find Vertica SQL website:

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ConceptsGuide/Other/SQLOverview.htm?tocpath=SQL%20Reference%20Manual|_____1

I follow the Vertica SQL examples and syntax, but for some examples, I cannot execute them in Celonis transformation statement editor part. Why this happens? Where can I find the exact documentation/manuals for Celonis transformation?

Thanks.

3 replies

J
Level 0
  • justin.stegm
  • Level 0
  • January 30, 2020
Hi David, you are right! Celonis uses Vertica for the transformations. It would be great if you could provide an example of what does not work and then we could debug it together. Best, Justin
.
Level 2
  • .14600878
  • Author
  • Level 2
  • January 30, 2020

Hi, Justin.

For example,

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/DataTypes/Date-Time/TIMESTAMP.htm?tocpath=SQL%20Reference%20Manual|SQL%20Data%20Types|Date%2FTime%20Data%20Types|_____7

SELECT (TIMESTAMP 2014-01-17 10:00 - TIMESTAMP 2014-01-01);

The output of Celonis is [object Object]; but I modified a little bit and it works:

SELECT TO_CHAR(TIMESTAMP 2014-01-17 10:00 - TIMESTAMP 2014-01-01);

Another things are how to implement in Celonis:

SELECT MAX(1,2,3);

Is it possible to set variables in Celonis?

Thanks.

.
Level 2
  • .14600878
  • Author
  • Level 2
  • January 30, 2020

Another question:

SELECT 1970-01-01 00:00:00::TIMESTAMP + INTERVAL 1572508986 second; --work in Celonis

SELECT T.TRANS_TIME || second FROM T; --work in Celonis

Note: T.TRANS_TIME are numbers, such as 1572508986

But the below statement doesnt work, why?

SELECT 1970-01-01 00:00:00::TIMESTAMP + INTERVAL T.TRANS_TIME || second FROM T

Error shows: Execution error: [Vertica]VJDBC ERROR: Syntax error at or near T

Thanks!

Community Code of ConductAccessibility statement