ZERO DATE VALUE PROHIBITED

[Claudio_Giam]

Hi all.
I have a problem because i can’t read record when the value is null.
i have modified the node connector, but it doesn’t work
thanks all.

[thor_landstrom]

Hey @Claudio_Giam,

It looks like you are trying to convert any zero dates to null. If you already tried adding a null conversion in your connector, have you verified if it applied it to any zero dates before passing the query results to DB reader?

I would just run a simple ‘SELECT * FROM …’ on the table in question to check if it was changed.

A possible fix is to try addressing it via query and handling the zero dates before passing it to the ‘DB reader’ node.

Here is a sample SQL query demonstrating the idea:

SELECT CASE 
    WHEN 'date column here' = 'how your zero date shows up' THEN NULL
    ELSE 'date column here' 
    END AS 'date column here'
FROM 'your table';

Hope this helps,
TL

[Claudio_Giam]

Hi thor_landstrom.
thank for your answer, but il doesn’t work. Like this:

[thor_landstrom]

The reason why it is not working is because that initial query: SELECT * FROM #table# AS 'table'
is together with the proposed query.

Typically you can separate them using a ; to show it is the end of a particular SQL statement, however in KNIME it is best to seperate them by nodes.

Here is a sample I did where I convert any value that is 0 to NULL using the statement I sent previously.

Here is my workflow (the first DB query has the initial SELECT *, the second has the CASE):

Before your SELECT *, I would place the CASE query first and then pass it to the DB Reader as it will overwrite over the SELECT *.

However, I noticed it looks like you are trying to replace ‘?’ which means it is already null in your attached image, so doing this will likely not do anything. Possibly the parameter you changed in your initial post ‘convertToNULL’ has converted it to null for a zero date time? I worked with sample data that was of format Date time and was able to run a DB reader successfully on null DATETIME data:

There could be some other values present that haven’t been converted to null that is causing the DB Reader to error out. I would look into your data and try converting any non standard or dates that look like zero date to null.

Hope this helps,
TL

[Claudio_Giam]

Hi Thor, thanks for your help.
I will try it.