Skip to Content

PI Is Substring function unreliable?

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

capture1.png (87.9 kB)
capture2.png (156.9 kB)
capture3.png (46.0 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Sep 01, 2017 at 02:49 PM

    Hi Elizabeth!

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

    Regards, Evgeniy.

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Sep 01, 2017 at 05:06 PM

    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 !

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 05, 2017 at 09:31 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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