topic [resolved] Adding Short type from database with null values in Talend Studio

[resolved] Adding Short type from database with null values

Anonymous — Thu, 17 Apr 2014 00:01:39 GMT

I'm fairly new to both Java and Talend. I've spent a couple hours now trying to find a solution to this.
I have a database with 2 columns:
CourseActions1 SmallInt NULL
CourseActions2 SmallInt NULL
When I bring those into Talend through a tMysqlInput, the values are brought in as a Java type Short.
I need to pass these into a different database table with 3 columns:
CourseActions1 SmallInt NULL
CourseActions2 SmallInt NULL
TotalActions SmallInt NOT NULL
You see that both database allows these values to be Null. There are several hundred rows of null values and they need to transfer as null into their respective field. But I need to add the 2 source fields to create the new Total column.
When I simply transfer these rows through a tMap, no problem. Null values are transferred as they should. But I cannot figure out how to add them together. I keep getting a null pointer error. I assume that's a Java thing where Short can't be null so (null + 0) results in an error.
Is there anyway to validate a null value against a Short type and replace it with 0 when adding?
I've tried this as an expression in the tMap component:
(row1.CourseActions1.toString() != null ? row1.CourseActions1 : 0) + (row1.CourseActions2.toString() != null ? row1.CourseActions2 : 0)
Any help is appreciated.

Re: [resolved] Adding Short type from database with null values

Anonymous — Thu, 17 Apr 2014 06:22:22 GMT

Hi sbaer,
Based on your post - you want to add whatever number comes in columns CourseAction1 and CourseAction2 and put its value inside TotalActions. During this ignore all null values. so following is the way
(CourseAction1?null:0:CourseAction1)+(CourseAction2?null:0:CourseAction2)
use above expression inside tMap for your TotalActions column.
Vaibhav

Re: [resolved] Adding Short type from database with null values

Anonymous — Thu, 17 Apr 2014 17:01:05 GMT

Thank you for your response.
When I placed your expression inside tMap, I got errors:
1. Cannot convert from Short to Boolean
2. Syntax error on token":", . expected
It doesn't seem that it recognizes this structure.
(CourseAction1?null:0:CourseAction1)

Re: [resolved] Adding Short type from database with null values

Anonymous — Thu, 17 Apr 2014 20:55:49 GMT

I was sure I had already tried this, but when I put this in just barely it worked.
(short)((row1.CourseActions1 != null ? row1.CourseActions1 : 0) + (row1.CourseActions2 != null ? row1.CourseActions2 : 0))
I checked each piece separately and didn't get errors. When I added the two together I got an error about converting type int to Short. So I found somewhere to add the (short) to the front to cast it, and all the rows were correctly inserted into the database.

Re: [resolved] Adding Short type from database with null values

Anonymous — Fri, 18 Apr 2014 06:01:58 GMT

Good...