handelng leading zeros

Hey Team, I have a Database (SAP) that I connect via the custom wrapper (RFC Calls). In this I have a column that contains leading zeros. It usualy contains int with 8 digits but itis saved as text with 10 char (2 leading zeros). Is it possible to map this to an integer without loosing the ability to search for it? In the end I want to have a REST Server that I can give the 8 digit int (might vary to 9 digit in the future) as a parameter and denodo will search SAP witht the 10 digit string (text). Example: Rest call: {baseURI}/logisticbatch?identifier=25579182 RFC: RFC function = RFC_READ_TABLE QUERY_TABLE = CHVW FIELDS = {WERKS,MATNR,CHARG,MJAHR,MENGE,XZUGA} OPTIONS = CHARG = '0025579182' Can I handle this with a regexp? or another string operation? Thanks!
user
01-02-2018 05:08:18 -0500

2 Answers

Hi: Sure thing! First, note that the RESTful web services include a search functionality by default. If you add a new Integer field to the output of your Derived View and use the [CAST](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/type_conversion_functions#cast) function to convert the CHARG field to an Int, then Denodo will apply the integer search to the underlying string its adapted from when you pass it as a search critera. If you want, you can remove the database string field and only expose the integer, so from your end users' perspective it is just one field. However, if this isn't possible because the RFC requires an input value for CHARG in order to retrieve data, then you'll need a View Parameter on your model, which needs to be padded with zeroes before you pass it to the RFC data source. There's a LEFTPAD function included in the "Denodo Xtrafuncs for VDP" package available on Denodo Connect, or you can do it manually with a combination of the [CONCAT](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/text_processing_functions#concat), [LEN](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/text_processing_functions#len), and [SUBSTR](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/text_processing_functions#substring-substr) built-in functions. Since your integer length can vary, I'd use something like this as my WHERE Condition to account for not only 8 and 9 digit numbers, but anything down to a one digit value; `logisticbatch.charg = substring(('000000000'||identifier) FROM len(identifier) FOR 10)` (The nine zeroes will offset by the length of the original integer, so when the substring finds its starting point we are always left with a 10 character string.) If, by chance, you need that view parameter to *not* be mandatory, assign it a default value of -1 and then switch your Where condition over to an expression. Wrap the existing clause in parenthesis and add a clause on the end to account for when users haven't specified a parameter; `OR identifier=-1` Hope this helps!
Denodo Team
02-02-2018 13:07:13 -0500
Thanks a lot for the answer! Even though I did not ask this question, it helped a lot for my project. Thanks, Anoop G
user
14-03-2019 13:34:21 -0400
You must sign in to add an answer. If you do not have an account, you can register here