Skip to Content

Issue when using LIKE_REGEXPR in Calculation Views

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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Jul 09, 2020 at 06:12 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.