Issue when using LIKE_REGEXPR in Calculation Views

[former_member679919]

Hi all,

I an working on a Graphical calculation view in HANA . we need to check if the input is a numeric or character.If the input is numeric then the leading zeros should be trimmed.

Since custom functions cant be used in Calculated Views, I have tried creating a calculated column and put the following logic in the Expression:

like-regexr.png

During validation it didnt show any errors. However when i tried to execute this View and see the raw data it is giving the following error.

boolean-error.png

However, when i executed LIKE_REGEXPR as a query , It did not give an error.

db-querylikeregexp.png

It is only giving this error when trying to use in The Calculation View. Please help me on this.

[lbreddemann]

Please post any code and test data as text, not as pictures!

You cannot expect anyone to type off your code from screenshots.

[lbreddemann · Accepted solution]

Looking at your pictures the problem seems to be that you try to use the LIKE_REGEXPR predicate where the case expression expects another expression is expected.

This predicate evaluates to a boolean which is what you find in your error message.

Now, does it make a whole lot of sense that this is not covered in syntax check upon activation of the model? Not at all. Maybe you want to open a support incident for this with SAP.

An equivalent logic can be implemented by using the LOCATE_REGEXPR function like so:

MAP(locate_regexpr (START '[^0-9\-\.\,\+e]' 
                     FLAG 'i'  
                     IN  "MATERIAL")
     , 0, ltrim ("MATERIAL", '0')
     ,  "MATERIAL")

This checks for any non-numeric characters and if the result is 0 (= none of those characters found) then the LTRIM() function is evaluated. Otherwise, the default option for the MAPping is used and the value of MATERIAL is passed through unchanged.

For a more extensive discussion of this you can check my blog post

What’s your number?

Obviously, this is quite a lot of value-by-value processing and not exactly helping your query performance. So, I'd recommend using this only as a last processing step right before the data is returned to the client and I'd definitively not recommend to perform joins on this computed column.

[former_member679919 · Accepted solution]

Thanks for your help Lars..