DataLink: Null converted to Zero on Import

3.88K 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

Mike, thanks for confirming this behavior. I believe there may be a work-around and I will try your suggestions.

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

0

Actually – this looks like something that occurs using DataLink against a database… In my test MYSQL database I NULL’d out a couple of values, and I can see the import via DataLink sets them to zero. I will investigate further and advise…

-Mike

0

Here is the .txt file:

Attachments

nullTest.txt
0

Hi – Thanks for posting.

I tried to replicate your issue without success. I am probably missing something. I have attached a .model and .txt file to show you what I did. Can you provide a .txt and .model that replicates the error?

Thanks…

-Mike

Attachments

nullTest.model