Unexpected output from tInformixInput

[Anonymous]

I'm attempting to use the tDBInput(Informix) component but I'm seeing weird behavior.

There is a field in our Informix database with the following values:

• (null)
• (blank or only contains spaces)
• N
• Y

The query for the tDBInput(Informix) component contains:

case
when trim(var_name) is null then 0
when trim(var_name) = '' then 0
when trim(var_name) = 'N' then 0
when trim(var_name) = 'Y' then 1
else 0
end var_name

The expected output for this column is 0 or 1. However, for some rows with var_name = (null), (blank), or "N", Talend is outputting a value of 1. When I query Informix directly with a query tool, such as DBeaver, the output is as expected. Only rows with var_name = "Y" result in an output of 1.

I used the tLogRow component to verify that the output of the tDBInput(Informix) component is producing the incorrect result, rather than somewhere else downstream in the job.

Has anyone else run into something like this?

[fdenis]

you may try to cast result as integer into your sql query.

[Anonymous · Accepted solution]

Thanks for the reply Francois! I gave your solution a shot but it didn't work because I wasn't paying enough attention to which environment I was working in. Our prod & non-prod were out of sync which threw me for a loop.

[Anonymous]

Hi,

   I don't have a personal Informix DB to check your issue quickly. But a quick work around will be to get the data in its original format from DB and do a transformation later in tMap using ISNULL() and .equals() functions.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved