LEFT & SUBSTRING functions not working in RPD

Community DW Integration

Hi,

OBI 11.1.1.9.0

Using OBI Admin Tool, I created 2 Logical Columns in BMM which is derived from an existing column using the below formula.  However OBI is still displaying the data from the source column (NOE_FAMILY_1 column) in both the logical columns.  The data from NOE_FAMILY_1 column is displayed as it is; as if the LEFT and SUBSTRING functions are not applied.

NOE_FAMILY_1 column contains strings (VARCHAR2).

Left("FIN"."D4 NOE"."NOE_FAMILY_1", 3)

Substring("FIN"."D4 NOE"."NOE_FAMILY_1"  FROM 6)

However if I use the same functions in OBI, it works as expected.

I am not sure what am I missing in the repository.

Thanks.

Community DW Integration

Can I please get some help with this issue?  I am going crazy :-(

Jerry Casey

Do you have to use server functions in the RPD?

There is no LEFT fnx, and the SUBSTR fnx doesn't use the "from/for" syntax.

The equivalent of LEFT('xxxxxx', 3) is SUBSTR('xxxxxx',1,3).

Community DW Integration

Jerry,

Thanks for your reply.  SUBSTR function cannot be used in the RPD.  Here is the error message I get.

LEFT and SUBSTRING are part of the string functions that I have to use. https://docs.oracle.com/cd/E14571_01/bi.1111/e10544/appasql.htm#BIEUG487

Thanks.

Community DW Integration

Sorry @Jerry Casey - silly mistake by me When I created the logical column, I right clicked the source column -> selected "Duplicate" -> then modified the newly created column with the function in "Derived from existing column using an expression" on the "Column Source" tab.

Today I decided to recreate this column from scratch and followed a different way.  I right clicked the logical table -> selected "New Object" -> then selected "Logical Column" -> then modified the newly created column with the above expression.  And this worked.

The difference I noticed is using the 1st way, it retained the source column mapping in the "Derived from physical mappings" section (see 1st image below)

Logical column that works.

Thanks all again.