DataLink: Null converted to Zero on Import

3.81K viewsDataLink
0

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?

0

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

You are viewing 1 out of 5 answers, click here to view all answers.