none
Fix Precision value after decimal in USQL code

    Question

  • Hi All,

    I am writing one U-SQL code where I have one column as a decimal, I want this column to give 4 precision after the decimal

    Below  example

    12.3  >> 12.3000

    12.43 >> 12.4300

    2       >> 2.0000

    I have used +0.0000m  to add the column, but I am not getting a consistent value for all records.

    Can someone help me with any function which can resolved my issue


    Thankx & regards, Vipin jha MCP

    Friday, January 11, 2019 1:39 PM

All replies

  • Hi Vipin,

    Have you tried using extractors in U-SQL ?

    Here are a couple of documents on Extractors :

    https://github.com/Azure/AzureDataLake/blob/master/docs/Release_Notes/2018/2018_Spring/USQL_Release_Notes_2018_Spring.md

    https://docs.microsoft.com/en-us/u-sql/functions/operators/built-in-extractors

    Have a look at these and let us know if it helps. Else, we can continue to probe in further.


    MSDN

    Monday, January 14, 2019 7:04 AM
    Moderator
  • I'll start off by saying I'm a C# hacker not necessarily a C# developer :)   ...but I created a custom assembly with a few helper methods in it, one of those is this that I use to format as you asked about:

            public static string DecimalSetScale(decimal decimalValue, int numberOfDecimals)
            {
                string decs = string.Concat(Enumerable.Repeat("0", numberOfDecimals));
                string rtnDecimal = "#." + decs;
                return (decimalValue.ToString(rtnDecimal));
            }

    I return it as a string datatype since I'm writing out to a file anyway and I can control the format.

    I then have a Testing project where I run these tests, for example:

            [TestMethod]
            public void TestMethod_DecimalSetScale()
            {
                Assert.AreEqual("123.45", Methods.DecimalSetScale((decimal)123.4467, 2), "decimal 2 digits round up failed");
                Assert.AreEqual("-123.45", Methods.DecimalSetScale((decimal)-123.4467, 2), "decimal negative 2 digits round up failed");
                Assert.AreEqual("123.00", Methods.DecimalSetScale((decimal)123, 2), "decimal 0 digits failed");
                Assert.AreEqual("1234.0125", Methods.DecimalSetScale((decimal)1234.0124681, 4), "decimal 4 digits round up failed");
                Assert.AreEqual("89.8315", Methods.DecimalSetScale((decimal)89.83145, 4), "decimal 4 digits round up failed");
            }

    Then in my U-SQL I issue a "USING ..." on my Assembly which I've registered and reference it something like:

    SELECT myAssembly.DecimalSetScale([OrderAmount], 4) ...

    The C# code could even just be added to your code-behind page perhaps?

    I hope this helps.


    Bill Blakey

    Wednesday, January 23, 2019 10:47 PM