12 Replies Latest reply: Feb 15, 2017 7:05 AM by Marek Waszczak RSS

    Decimal issue while fetching data into QVD file

    Marek Waszczak

      Hi,

       

      I have noticed some issue on the QV server.

      I am fetching data from mySQL database via ODBC connection to the QVD file using the following scritp

       

      SET ThousandSep='.';

      SET DecimalSep=',';

      SET MoneyThousandSep=',';

      SET MoneyDecimalSep='.';

      SET MoneyFormat='£#,##0.00;-£#,##0.00';

      SET TimeFormat='hh:mm:ss';

      SET DateFormat='DD/MM/YYYY';

      SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

      SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

      SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

       

      ODBC CONNECT TO ConnectionName (XUserId is ************************, XPassword is ***********************);

      //example of 1 day data for 1 user

      let vTableName ='day_record'; //define the table name

      QUALIFY *;

      $(vTableName):

      LOAD

        "dv_u_category",

          "dv_u_task",

          "u_task",

          "dv_u_user",

          "sys_id",

          "u_date",

          "u_hours",

          "u_time_card",

          "u_user";

      SQL SELECT "dv_u_category",

          "dv_u_task",

          "dv_u_user",

          "u_task",

          "sys_id",

          "u_date",

          "u_hours",

          "u_time_card",

          "u_user"

      FROM "SCHEMA".OAUSER."$(vTableName)" WHERE sys_updated_on BETWEEN '2016-12-01 00:00:00' AND '2016-12-01 23:59:59' AND  dv_u_user LIKE 'USER001';

      UNQUALIFY *;

       

       

      The problem I cant solve is that If I run the script from my laptop, data in the u_hours looks like that:

      u_hours

      0

      0

      0.5

      2.25

      0.25

      0

      4

      0

      0.25

      0.25

      0

      0

       

      but the same script used on QV server returns this:

      u_hours

      0

      0

      0

      2

      0

      0

      4

      0

      0

      0

      0

      0

       

      De ODBC has been configured ages ago both on the server and my laptop at the same time (never changed any configuration or local settings)

      What can be the root cause of this issue ?

       

      I have tryied swiched comma and dot in the file on the QV server

      SET ThousandSep='.';

      SET DecimalSep=',';

       

      it didn't help