Values defaults to 0 instead of null

[Anonymous]

Hello,

I am performing checks to see if data in DB and in file are same. 

DB_input -> This is the hash containing data from DB

ebal_nl -> File containing data to compare with

Both are connected via a tmap.

I am running inner join with unique match and file is main in tmap and db is the lookup in tmap. There are 4 rows in file which are not there in DB. When the comparison occurs, I see instead of nulls for records not present in DB but present in file, the values gets default to '0'. Please let me know if anyone has faced this issue ?

[Anonymous]

Hi,

     You have specified the details of both file and DB during data read. But the difference has occurred when the data was created in file/DB. Could you please advise how the data is getting populated in both areas? Could you please share the details of the job flow and the component details?

    Now another query is whether the column you are comparing in DB is having any not null or primary key constraint? If it is present, then the data with null values will not be loaded to Database. So it might not be an issue but a data constraint you must have added either at DB layer or at schema level in Talend components. More screenshots will be helpful in detailed analysis of this scenario.

   Could you please share the above details and we will take it from there?

Warm Regards,

Nikhil Thampi

[Anonymous]

Thank you for response.

Please see attached screenshots of tmap comparisons.

I have data from file as main and db as lookup. There are some records in file which are not in DB. So, instead of getting nulls for records not in DB, I am seeing it as 0.0 (because it is data type is double).

I am not setting as 0.0 default value anywhere. 

[Anonymous]

Hi,

     I believe you are referring to pergen_sales as the problmeatic column where you are getting 0.0 instead of null. Could you please add a tlogrow between the hashinput component (which is storing the DB result) and tmap and see whether the thashinput component itself is generating data in column as 0.0?

     If the answer is yes, you will have to verify how the data is getting loaded to the Hash buffer and in one of those steps, there should be an entry to convert null as 0.0. Could you please share the data load job flow as the next step of analysis?

Warm Regards,

Nikhil Thampi

[Anonymous]

Hi,

Thank you for response.

Yeah, that is the column which is indeed problematic.

I added a tlogrow between hashinput component (which is storing the DB result) and tmap. But, I see the corresponding datetimes which are present in file is not present at all (so no such values). So, it means to say from DB component there are no rows. Also, from DB to hash, I have not added any data transformations at all.

So, in short while loading data from DB to hash input I have not made any transformations to convert nulls to 0 and also I do not get any data from DB which has nulls. While in file there are rows which are not in DB. So since there is data in file which is not in DB, I need to print that a column in file has value 'xyz' while in db value 'null'.

Also, please let me know how you came to know that pergen_sales was the column which was having issues ? 

[Anonymous]

Hi Arun,

     Could you please remove the sensitive information from your job and share it as a zip file for detailed analysis? I will open from my Studio and see the job flow.

     Regarding your last query, Sales is always a double column. So it was easy to identify 🙂

Warm Regards,

Nikhil Thampi

[Anonymous]

Hi,

Please see the attached zip file "export_to_talend.zip" exported using export items.

Also, please see attached file exported using build job "export_to_talend_0.1_build_job.zip".

Please let me know if I have made any mistakes ?

Thanks

[Anonymous]

Hi Nikhil,

Please let me know if you have any updates on the same ? Please note that it was just to check if am making any mistake.

Thanks.

[Anonymous]

Hi Arun,

        I didn't get a chance to look the details since I am doing an overnight travel tonight 😞

        But let us create a quick resolution for your specific use case as an interim task. 

         Having said that, your specific use case is that if the data from the incoming file is not present in DB, you would like to put null for the output columns. In this scenario, you do not have to carry all the columns from lookup to the reject flow. Because we already know that the data has reached reject flow due to absence of matched records.

        So you should pass the key values from the main flow to the reject table (if I remember correctly, it will be row2) and use it for further processing. You do not have to carry forward any data from your lookup flow for inner join rejects.

        Now another query you might be having in your mind is what about the reject records based on the expression given in main output flow. I was able to populate null to a double variable in inner join reject as shown below (please refer tlogrow2).

 

So it is not a tool limitation since we are able to automatically populate null to double data type columns. Since I do not have all the sample files and database connections, I was not able to quickly check it. But I will go through the code flow later to verify whether there is any substitution somewhere in the flow.

Warm Regards,

Nikhil Thampi

[Anonymous]

Hi Nikhil,

Thank you, so mostly I am making substitution without my knowledge somewhere. Will check again, please check when you get time so that I can get help.

Thanks.