0

For our customers that use SAPB1 with HanaDB we execute the same query to pull data out (using the ADO.NET interface). Some have their regional system systems that appear to control numerical formatting according to their locale, etc.

Say for example when executing: SELECT RDR1."Quantity" FROM RDR1

  • For a customer in the USA, RDR1."Quantity" = 2.000000
  • For a customer in Germany, RDR1."Quantity" = 2,000000 (notice the comma vs decimal)

I assume the actual database storage of the value is the same, it is just the query engine that is applying default system formatting based on the settings that makes the end result formatted one way or another.

Now, using a brute force approach and per column this type of change can be made: REPLACE(CAST(ROUND(RDR1."Quantity",2) AS DECIMAL(10,2)), ',', '.') AS "Quantity” to give me "2.00"

However we have many columns like this and we want to make the code usable in all cases vs having multiple instances of the same queries for different regions -- and it just seems there should be a way to tell the query engine to return values as if it were in the USA and ignore default system locale and currency settings.

Question: Is it possible to make a high level setting change instead of per column formatting/casting - either using Hana SQL query code (like a session variable or something) or a connection setting when we establish the ADO.NET connection to HanaDB that will just make the values come out as if they were in the USA?

I haven't found any yet but perhaps it is not so easily found in the documentation how to handle it.

Lars Br.
  • 9,949
  • 2
  • 15
  • 29
Streamline
  • 2,040
  • 4
  • 37
  • 56

2 Answers2

0

No, there is no HANA setting that would define how the application, SAP Business One in your case, renders the data to the user screen.

For SAP B1 you can find the documentation for setting these user settings here. These settings include "thousands separator", "separator", "decimal places", and others.

But on HANA DB level, there is no global setting for that.

Also, the REPLACE workaround mentioned turns the input number data into a string, losing all the number semantics (ordering, mathematical operations) and increase the memory requirements for those values. Rather avoid this technique if possible.

Lars Br.
  • 9,949
  • 2
  • 15
  • 29
  • Hi Lars, thanks for your input. We are executing directly w/HanaDB, we don’t interact w/B1. If you are suggesting to avoid that technique of the “REPLACE” in a query, what technique would you recommend if the client app consuming the data output from the query expects to use data using numerical formatting based on USA settings? Also, do you think the setting controlling this number behavior is on HanaDB (or the server where it’s running) or on the server where the .NET app is running and if we were able to change the settings for the computer where our app is running would it have any affect? – Streamline Feb 23 '21 at 13:59
  • Lars, after re-reading your post I think I see that you might have interpreted the workaround as a way to change the data in HanaDB - it is not. It is only a workaround to change the data as it is output from the query to the client app via a TAB separated text file where it will be read back in and the stringified decimal will be converted be into an actual decimal. Its life as string is only momentary as it is output. – Streamline Feb 23 '21 at 14:17
  • 1
    I suggest that you change the display settings of whatever client app you’re using. Again: there’s no “server-side” setting. Please review your understanding of Locale settings for users. – Lars Br. Feb 23 '21 at 20:57
  • Lars, I am well aware of locale settings. And it is precisely those settings that have to be causing the output to be the way it is. Our client app has no display and there is no "user" other than a DB user to connect, it just exports the data from HanaDB (via an ADO.NET connection) to a local text file on a computer in the customer environment. If you open that file in a text editor you can see clearly that it has the locale specific formatting. If by user you mean OS user, ya - we can't change that for the customer which is what I think is influencing either the HanaDB engine or the .NET app – Streamline Feb 24 '21 at 02:41
  • To clarify and simplify my above comment (that I can't edit now), our client app is a console app (no display) that just executes a query through a ADO.NET connection and writes the output to a text file. Somewhere between the HanaDB and the writing of the output to the file the locale setting on one of the two computers has to be involved, that is clear. The issue is visible in the text file as soon as it is written out. – Streamline Feb 24 '21 at 03:29
  • For the output formatting, it doesn't really matter if your application puts the data into a file or onto the screen. The point is, that the formatting happens in your application. If you bind the results of the database query to the corresponding .NET data types, then SQL "numbers" will end up in numeric data types. Only when you print them out to a text file, the data types disappear and only characters remain. Have a look at the function that writes into the log file and check the type conversion from numeric to text before printing it out. – Lars Br. Feb 24 '21 at 05:31
  • Concerning "our app has no user" - it does get executed in an OS session of an OS user, right? *That* OS users locale setup is what you need to check then. – Lars Br. Feb 24 '21 at 05:33
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/229136/discussion-between-streamline-and-lars-br). – Streamline Feb 24 '21 at 05:43
0

If you want to alter the decimal separator of the program output, just tell it to .NET. An option is to set the Current Thread's Culture like in this snippet:

using System;
using System.Globalization;

class Program
{
    static void Main(string[] args)
    {
        decimal d = 2021.1002m;
        
        Console.WriteLine("Current Culture is {0}.", System.Threading.Thread.CurrentThread.CurrentCulture.Name);
        Console.WriteLine("d = {0}.", d);
        
        System.Threading.Thread.CurrentThread.CurrentCulture =
            new CultureInfo("en-US");
            
        Console.WriteLine("Current Culture now is {0}.", System.Threading.Thread.CurrentThread.CurrentCulture.Name);
        Console.WriteLine("And d = {0}.", d);
    }
}

For me the ouput is:

Current Culture is es-ES.
d = 2021,1002.
Current Culture now is en-US.
And d = 2021.1002.
Khin
  • 11
  • 2