PI Is Substring function unreliable?

[elizabeth_stapleton]

capture1.pngcapture2.pngcapture3.pngHi experts,

I am wondering if the substring function within PI mapping is unreliable. Or perhaps I am just using it wrongly (is that possible?)

I have a source field mapping across to target. This mapping is part of a JDBC connection to an external database, which appears to have difficulties with the strings of 2 fields being too long.

To counteract this I have put a max length (of 20 and 12 respectively) on each data type field. Then I built a check in the mapping involving substring.

If the length of the source is greater than 20 (or 12) then map across substring(0 for 20), otherwise map across the whole source field.

Looks fairly straight forward, however:

When running for real, nothing comes through to target.

When running in test mode the value comes through to target.

When I change my mapping to use constants...i.e. when Source length is greater than 20 map across "Greater than 20" otherwise "20 or less".

Interestingly (??) when I run this in test mode, my test data comes through as "20 or less" (which it is), but when running for real it maps as "Greater than 20".

This last comment aside, can anyone suggest a reason why using substring refuses to map anything across in the real runtime environment?

It mapped fine when it was a direct map, it just failed to get into the target database.

Suggestions gratefully received.

I have attached some screen shots.

Many thanks

Elizabeth

[former_member190293 · Accepted solution]

Hi Elizabeth!

First, try to insert Trim function before checking the length. And use the trimmed value when passing to substring function.

Regards, Evgeniy.

[elizabeth_stapleton · Accepted solution]

Hi Evgeniy,

Trim is indeed what I needed to ensure that my value comes through to the mapping target field. Thank you.

Following on from that I have now proved that using Substring(0,20) on a value going to an external database, into a field of nvarchar 20, doesn't work. I have actually had to reduce my value to Substring(0,17) to fit in the final destination field. (causing truncated errors)

Can you provide any pointers as to why this is so please?

Thanks

Elizabeth

[elizabeth_stapleton · Accepted solution]

As further comment to the above about the 17 char limit......

When the data comes the official route, through the abap program, pi and then the external database, 17 chars is all it can pass through.

When I interrupt the flow, by ignoring the abap sent data, and allocating a constant in the mapping, 20 characters can be sent to the database.

What goes on here, any ideas?

Thanks

Elizabeth

[elizabeth_stapleton · Accepted solution]

Actually, I've probably solved my last comment. I wasn't using Trim when the length was 20 or less, only when more. I have included Trim on both true and false routes through my mapping. It is certainly looking better. So ultimately it looks like it was the Length function that was unreliable, not the substring one.

I shall have to get it into the QA environment to prove I've fixed it, because the test data is limited.

Thanks for your help

Elizabeth

[adalid]

Hi Elizabeth,

The problem you are facing is with the greater and ifelse functions, so let's break it down:

Regarding Greater function, this function takes two input values and compares them. If the first value is greater than the second one, the output is 'true' else 'false'.

So taking your example

SAPCaseHouseID = ‘7777 Waverty Read’ <-our first value

Length = 17

We compare here with your constant = 20 <-our second value

17 is greater than 20 ? No, so output is false as binary we will go with 0

Now let’s check your If…Else.... function

If greater (which we know is false so zero) then go mapping across using substring "position 0… characters.. 20 " but as is zero what you get is empty... hope this responds your question.

Adalid, saludos !

[elizabeth_stapleton]

Hi,

Firstly, thank you, Trim solved the problem of getting truncation errors at the external database side of the interface.

However, this has introduced a new problem.

What we have is a 20 char field, being passed from an abap program.

The 20 char field is made up of House number and Street name with a space char coded inbetween

I am finding that Trim cuts off the field content after the house number.

It does not cut off where there is just a street name, e.g. 22 High Street comes out as 22

But for addresses with no house number.....Smedley John Street - comes out as Smedley John Street.

Obviously there is some difference in the "space" characters in the 2 examples.

I am not really wanting to fix the abap by getting the programmer to use another delimiting character, and replacing that within the mapping, as I might have other scenarios where we have no control over what is passed to us (so would like to find the solution).

I also notice that when I run this in test mode, it passes the full address line ok

Any suggestions would be gratefully received

Thanks

Elizabeth

[weberpat]

This sounds weird. I don't have a PI system at my disposal right now to verify the behavior but a "trim" should clearly remove spaces at either end of the string only.

You could try the Java String.trim() method in a UDF or use String.replaceAll().

Trimming spaces from the beginning of the String:

yourString.replaceAll("^\\s+", "");

Trimming spaces from the end of the String:

yourString.replaceAll("\\s+$", "");

[elizabeth_stapleton]

Hello Patrick

This was really weird. I did as you suggested, and created a java UDF, but I still got the same problem. Though again, it worked fine in test mode, just not with an end to end test.

Ultimately I have resolved the issue by going back to the abap programmer, getting them to output house number and street in 2 separate fields, and then trim()-ing them individually, before I concatenate them into one field to go to the database.

Now it all works fine.

Many thanks for your input.

Elizabeth