DataLink: Null converted to Zero on Import
When I import numeric data that contains a null, the null is converted to a zero when imported by the DataLink.
This is causing problems in fields where zero should be different from null. For instance, I have user “override” fields. A null means the user chooses not to override. A zero means they override with zero (which is different from null in this case).
Is there a way to prevent Quantrix DataLinks from converting null to zero for numbers?
OK – Basically what is happening is that if Quantrix identifies the database column as a number column, then it treats NULL’s as Zero’s. If Quantrix identifies the database column as a text column, it treats the NULL’s as NULL.
Quantrix needs to handle the NUMBER NULL’s better. This will be considered for the 3.0 release.
2 potential workarounds.
You could write a CASE function in your SQL statement that takes any NULL’s and turns them into a different number besides zero. This statement would take any NULL’s and turn them into -1.
SELECT
table1.item,
CASE
WHEN table1.value is NULL THEN -1
ELSE table1.value
END
FROM
table1 table1
Or via the CAST function you could turn everything into a text string, and the NULL’s would come across as NULL.
SELECT
table1.item
CAST(table1.value AS CHAR)
FROM
table1 table1
Either of these approaches could be implemented via the DataLink SQL Wizard – expert mode.
Let us know if this helps at all.
-Mike
