Decimal issue while fetching data into QVD file

[waszcma1]

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

[swuehl]

Marek Waszczak wrote:

I did it for purpose

Converting numeric value to the char we have no doubt how to display this value and shows it as a text so there is no way that the numbers after coma can gone.

I don't fully understood this approach.

I would suggest to read in numbers as numbers and handle formatting on the QV side, i.e. remove the CAST()  from your DB SELECT.

[waszcma1]

I have load and joined the data from 2 qvds one was generated on laptop and one from the server

there is no doubt that on the server all numbers after dot are missing while is loaded via ODBC.

[waszcma1 · Accepted solution]

The new version of ODBC has been installed on the QV server the problem is not occurring anymore.

Problem solved