Question Re: DWC : Inconsistent datatype; Decimal is invalid for function seconds_between in Technology Q&A

DWC : Inconsistent datatype; Decimal is invalid for function seconds_between

former_member183290 — Tue, 27 Dec 2022 14:56:07 GMT

Calculated KPI Timestamp difference: In DWC

Hi Everyone,

I was trying to get a solution for the below & tried searching the forums. Tried various functions to get the desired output but no luck. Could you please look at the below and share what might be going wrong / how should this be achieved?

I have data coming in from S4HANA --> DWC. I have 2 timestamp fields, say Creation & Completion timestamps of Data type ‘DECIMAL’ (It's from S4H source table itself)

------------------------------------

I am trying to do the calculations in DWC to get the processing time.

When I use, SECONDS_BETWEEN function (datatype changes from Decimal) & It gives me an error stating that "Error- inconsistent datatype: at least one of the following argument types DECIMAL or DECIMAL is invalid for function seconds_between:"

Anything that I use to calculate difference is not working. Some functions are not accepted in DWC and others give me the error as stated.

Any idea, on how this has to be implemented? It helps.

Re: DWC : Inconsistent datatype; Decimal is invalid for function seconds_between

amoghkulkarni — Tue, 27 Dec 2022 18:07:00 GMT

Hello madhavvikranth

As the column is of type - Decimal, the following type casting needs to be done -

SECONDS_BETWEEN(TO_TIMESTAMP(TO_VARCHAR(TO_BIGINT(CreationTS)),'YYYYMMDDHHMISS'),TO_TIMESTAMP(TO_VARCHAR(TO_BIGINT(CompletionTS)),'YYYYMMDDHHMISS'))/60/60

Here is the output -

Regards,

Amogh K

Re: DWC : Inconsistent datatype; Decimal is invalid for function seconds_between

former_member183290 — Wed, 28 Dec 2022 07:10:55 GMT

Thanks a lot, Amogh. This worked & this is what we are expecting.

We will further be fine tuning this KPI for giving a meaningful info to Business in HH:MM:SS. But, the purpose is achieved in first place.