It is important to understand how SQList exports numbers, especially when they have decimals and/or are defined to be displaied as percentages.
The basic rule for decimal positions it that SQList will use the column definition to determine how many decimals the correspondent column in the SQL table will contain:
- when the "Number of decimal places" is set to 1, 2, 3, etc., SQList will export the column as a DECIMAL(18, 1), DECIMAL(18, 2), etc.;
- when the "Number of decimal places" is set to "Automatic", SQList will export the column as a FLOAT rather than a DECIMAL;
- when the "Number of decimal places" is set to "Zero", SQList will export the column as a INT rather than a DECIMAL.
Something else happens however, when the column is also set to "Show as percentage": in this case, SharePoint will not store the number "as you see it on the screen" but it will store it divided by 100. This will increase the actual number of decimals by two digits.
Warning: Because the SharePoint web services return numbers as they are stores, when SQList exports them to the SQL table there may be a loss of precision due to the actual precision of the number being higher than the precision defined by the column.
To work around the issue of loss of precision, you can simply increase the number of decimals for the column (e.g. from 2 to 4) at which point the whole number will be stored in the SQL table. However, as we understand that this is not an ideal solution, we are adding a feature to automatically handle this situation in version 4.3 of SQList.
This table lists all combinations for the number 123.4567:
| Number of decimal places |
Show as percentage |
Stored by SharePoint as |
SQList exports it as |
SQL type |
| Automatic |
No |
123.4567 |
123.4567 |
FLOAT |
| Zero |
No |
123.4567 |
123 (rounded) |
INT (by default)
or
DECIMAL(18, 0)
(user configurable) |
| 1 |
No |
123.4567 |
123.5 (rounded) |
DECIMAL(18, 1) |
| 2 |
No |
123.4567 |
123.46 (rounded) |
DECIMAL(18, 2) |
| 3 |
No |
123.4567 |
123.457 (rounded) |
DECIMAL(18, 3) |
| 4 |
No |
123.4567 |
123.4567 |
DECIMAL(18, 4) |
| |
|
|
|
|
| Automatic |
Yes |
1.234567 (divided by 100) |
1.234567 |
FLOAT |
| Zero |
Yes |
1.234567 (divided by 100) |
1 (rounded) |
INT (by default)
or
DECIMAL(18, 0)
(user configurable) |
| 1 |
Yes |
1.234567 (divided by 100) |
1.2 (rounded) |
DECIMAL(18, 1) |
| 2 |
Yes |
1.234567 (divided by 100) |
1.23 (rounded) |
DECIMAL(18, 2) |
| 3 |
Yes |
1.234567 (divided by 100) |
1.235 (rounded) |
DECIMAL(18, 3) |
| 4 |
Yes |
1.234567 (divided by 100) |
1.2346 (rounded) |
DECIMAL(18, 4) |
| |
|
|
|
|
