number of 17 digits

[ali_hijazi]

hello I'm reading data from an AS400 database

there is a field that contains account numbers composed of at most 17 digits

when I display these values I see something like the following:

1.5688946100202e+016

9009342456

1.5688946100202e+015

1.5688946100202e+014

what I want is to display the number as is if if its originally 17 digits and when it is composed of less than 17 digits I want to add leading zeroes

I used the following function

num(field,'00000000000000000')

the first number returned blank and the rest were well transformed

please advise of what to do

[ali_hijazi]

I tried it on the following number 1.5688946100202e+016 and it returned 15688946100202000 I think this is correct

[ali_hijazi]

I have the following number:15688946100202001 your function combination returns 15688946100202000  needs modification please advise

[hic]

If you want to keep all digits, you should use Text(Field)

HIC

[ali_hijazi]

i already tried it but it returns 1.5688946100202e+016 as text

[pgrenier]

Hello Ali,

I unfortunately don't have access to an AS/400 database to verify this, but maybe if you converted the data to text format in your native query on the source AS/400 database, then interpret the result as Text data, you might get the expected result.

LOAD Text(NewField) as Field;

SQL SELECT LPAD(CAST(Field AS CHAR(10)), 17, '0') AS NewField FROM AS400_Table;

Regards,

Philippe

[#7]

Hi philippe,

Thanks a lot for your code. It really worked for me. I was really struggling for 3 days and atlast i succeeded.

Thanks,

Sase

[flanfranco]

I'm faced same problem, i resolve it in the ETL (SQL SELECT), retrieving the INTEGER field in VARCHAR, for example:

example_table:

SQL SELECT *,

    convert(varchar(), example_field_integer) AS Example_Field_string

FROM example.dbo."example_table";

STORE example_table into $(VPath)\ETL\QVDs\example_table.qvd (qvd);

DROP Table example_table;

I hope help you!

Greetings!

Flavio