Skip to main content
Question

DATA INTEGRATION - DELTA EXTRACTIONS.

  • November 29, 2023
  • 6 replies
  • 30 views
D
Level 2
Forum|alt.badge.img+5

I want to extract a table "SRRELROLES" from SAP system. For this table, I have to use dynamic and private parameters on the column "UTCTIME" UTC Timestamp in Short Form (YYYYMMDDhhmmss).  I get an error for delta extraction since the data type I use is date (I have tried integer and double but neither worked)and the data type of UTCTIME is float according to SQL vertica in Celonis and UTCTIME has data type DEC in the SAP system.

 

My question is which datatype can I use for my dynamic and private parameters? Or how can I define a variable inside the FILTER STATMENT or DELTA FILTER STATMENT in order to get the max of the UTCTIME and use it for my delta extractions?

6 replies

dennis.pflug
Level 10
  • dennis.pflug
  • Level 10
  • November 29, 2023

Hi,

 

we have it setup via double and it is working.

 

We have create a parameter (startDate_Decimal) with data type "Double" with a value like this: 20210101000000,0000000

 

Within the data jobs I use a parameter srrelrolesLastLoadDate that is from type Dynamic, Data Type and takes the max UTCTIME date from SRRELROLES.

The fallback default value is coming from startDate_Decimal.

 

And this is how our filter statement and delta filter statement looks like:

 

Filter Statement:

UTCTIME >= <%=startDate_Decimal%>

 

Delta Filter Statement:

UTCTIME >= '<%=srrelrolesLastLoadDate%>'

 

Hope this helps.

 

BR

Dennis

D
Level 2
Forum|alt.badge.img+5

Hi,

 

we have it setup via double and it is working.

 

We have create a parameter (startDate_Decimal) with data type "Double" with a value like this: 20210101000000,0000000

 

Within the data jobs I use a parameter srrelrolesLastLoadDate that is from type Dynamic, Data Type and takes the max UTCTIME date from SRRELROLES.

The fallback default value is coming from startDate_Decimal.

 

And this is how our filter statement and delta filter statement looks like:

 

Filter Statement:

UTCTIME >= <%=startDate_Decimal%>

 

Delta Filter Statement:

UTCTIME >= '<%=srrelrolesLastLoadDate%>'

 

Hope this helps.

 

BR

Dennis

Hello Dennis,

 

thanks for your insightful answer. I still have the problem with the delta extraction and get the error

 

Error mapping table extractions Error parsing filter: Number Format Error For input string: "20231130095213" caused by: For input string: "20231130095213"

 

apparently, the max UTCTIME returns a string instead of a double value which causes the problem. I have specified my srrelrolesLastLoadDate to be double and dynamic.

 

Any idea what the issue is?

A possible solution for me will be somehow inserting a query inside the DELTA FILTER STATEMENT and defining a variable max which I can use for my delta filter statement:

this may look something like this

max_srrelroles = MAX(UTCTIME) -- Assuming UTCTIME is from the existing extractions

and UTCTIME >= max_srrelroles -- The actual extraction

 

Cheers,

Dilyar

dennis.pflug
Level 10
  • dennis.pflug
  • Level 10
  • November 30, 2023

Hello Dennis,

 

thanks for your insightful answer. I still have the problem with the delta extraction and get the error

 

Error mapping table extractions Error parsing filter: Number Format Error For input string: "20231130095213" caused by: For input string: "20231130095213"

 

apparently, the max UTCTIME returns a string instead of a double value which causes the problem. I have specified my srrelrolesLastLoadDate to be double and dynamic.

 

Any idea what the issue is?

A possible solution for me will be somehow inserting a query inside the DELTA FILTER STATEMENT and defining a variable max which I can use for my delta filter statement:

this may look something like this

max_srrelroles = MAX(UTCTIME) -- Assuming UTCTIME is from the existing extractions

and UTCTIME >= max_srrelroles -- The actual extraction

 

Cheers,

Dilyar

hmm interesting.

When I check data type of UTCTIME via:

 

SELECT column_name, data_type FROM COLUMNS WHERE table_name = 'SRRELROLES';

 

I get as result UTCTIME -> float

 

Why is your column handled as String?

In standard SAP environment UTCTIME should be of type DEC: SRRELROLES SAP (Object Relationship Service: Roles) Table - ABAP field list (se80.co.uk)

D
Level 2
Forum|alt.badge.img+5

Hello Dennis,

 

thanks for your insightful answer. I still have the problem with the delta extraction and get the error

 

Error mapping table extractions Error parsing filter: Number Format Error For input string: "20231130095213" caused by: For input string: "20231130095213"

 

apparently, the max UTCTIME returns a string instead of a double value which causes the problem. I have specified my srrelrolesLastLoadDate to be double and dynamic.

 

Any idea what the issue is?

A possible solution for me will be somehow inserting a query inside the DELTA FILTER STATEMENT and defining a variable max which I can use for my delta filter statement:

this may look something like this

max_srrelroles = MAX(UTCTIME) -- Assuming UTCTIME is from the existing extractions

and UTCTIME >= max_srrelroles -- The actual extraction

 

Cheers,

Dilyar

Exactly. This is the initial point, I was making at the beginning of this thread.

dennis.pflug
Level 10
  • dennis.pflug
  • Level 10
  • November 30, 2023

I tried it without any parameter by just using hard coded values e.g. UTCTIME >= 20231101000000

 

If I use it like this UTCTIME >= 20231101000000 I also get the Error parsing filter: Number Format Error For input string: "20231101000000" caused by: For input string: "20231101000000" message.

 

If I use single quotes before and after the date it is working for me: UTCTIME >= '20231101000000'

 

Have you checked to use single quotes for your parameter as well?

Same as in my example above?

 

UTCTIME >= '<%=srrelrolesLastLoadDate%>'

D
Level 2
Forum|alt.badge.img+5

I tried it without any parameter by just using hard coded values e.g. UTCTIME >= 20231101000000

 

If I use it like this UTCTIME >= 20231101000000 I also get the Error parsing filter: Number Format Error For input string: "20231101000000" caused by: For input string: "20231101000000" message.

 

If I use single quotes before and after the date it is working for me: UTCTIME >= '20231101000000'

 

Have you checked to use single quotes for your parameter as well?

Same as in my example above?

 

UTCTIME >= '<%=srrelrolesLastLoadDate%>'

This helped thanks a lot. The single quotes were missing.

Community Code of ConductAccessibility statement