EVALUATE function with REGEXP_SUBSTR dbfunction not working as expected within OTBI reporting

Satyavrat_Savarn

Hi,

We need to use REGEXP_SUBSTR function to get 6th segment value from Depreciation Expense Concatenated Segments. For example, we have a concatenated segment value of '3804-632055-0-250400-0-0-0-0-0-0-0-0' available and I want to get the 6th segment value, so we are trying using following function.

REGEXP_SUBSTR(<concatenated segment>,'[^-]+',1,6) function. This function works fine in normal SQL query, however if same can be persuaded with OTBI under EVALUATE function, it throws following error.

Formula syntax is invalid.

[nQSError: 10058] A general error has occurred. (HY000)

[nQSError: 43113] Message returned from OBIS. (HY000)

[nQSError: 27002] Near : Syntax error (HY000)

[nQSError: 26012] . (HY000)

SQL Issued: SELECT EVALUATE(REGEXP_SUBSTR(%1,'[^-]+',1,6) as char,"Asset Depreciation Expense Account"."Depreciation Expense Concatenated Segments") FROM "Fixed Assets - Asset Depreciation Real Time"

Please note we can not use normal substr function in this case because the segment length is varying for multiple segments.