You can translate the question and the replies:

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!
01-02-2018 05:08:18 -0500

4 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]( 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](, [LEN](, and [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
14-03-2019 13:34:21 -0400
Hi team, I need help with few questions listed below: I am connecting to SQL server and for the numeric(19,8) datatype the decical value is lost at denodo side. How to maintain the decimal precision at denodo side? SQL side uptp 8 decimal places -- 550000.00000000 7185205.58000000 denodo side all the trailing zeros are truncated -- 550000 7185205.58 secondly, the datetime field in sql has precison upto nano second. How to maintain the datetime value upto nano second level? SQL- 2013-09-26 12:40:07.000 but in denodo side its trimming the nano second value denodo - 2013-09-26 12:40:07 Thanks
28-05-2021 14:57:25 -0400
Hi, This appears to be a duplicate question of [Data precision]( You can follow the latest updates on the original question. Hope this helps!
Denodo Team
09-06-2021 17:42:35 -0400
You must sign in to add an answer. If you do not have an account, you can register here