Five Stars

Int column having Null values

Hi,

 

I have a problem with Null values present in my Integer column.

FLow :

 

Input(.txt )--->Tmap--->Snowflake ouput

 

Tmap : using the variable

 

row5.Column28==null||row5.Column28.equals("")?null:new Integer(row5.Column28)

 

But giving me an error :

NumberFormatException: For input string: "NULL".

 

Could you please let me know what am I doing wrong ?

 

TIA

7 REPLIES
Fifteen Stars

Re: Int column having Null values

row5.Column28 is a String of the value "NULL". This would meet your criteria of it not being null and it not being equal to an empty String. You need to check if your String is numeric. There is no Java function for this, but you can create one. Here is an example you could use (set it up in a routine)....

 

public static boolean isNumeric(String strNum) {
    boolean ret = true;
    try {

        Integer.parseInt(strNum);

    }catch (NumberFormatException e) {
        ret = false;
    }
    return ret;
}

Assuming this were to be added to a routine called MyUtils, your code would change to....

routines.MyUtils.isNumeric(row5.Column28) ? new Integer(row5.Column28) : null
Rilhia Solutions
Six Stars

Re: Int column having Null values

Hi @nbhandari,

 

you can do so without any help of routine as well.

 

while reading input.txt make column28 datatype change to String type. And in tmap write below code:

 

row5.Column28.equalsIgnoreCase("NULL") ? null : Integer.parseInt(row5.Column28)

Regards
Aashish
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Fifteen Stars

Re: Int column having Null values


@aashish wrote:

Hi @nbhandari,

 

you can do so with any help of routine as well.

 

while reading input.txt make column28 datatype change to String type. And in tmap write below code:

 

row5.Column28.equalsIgnoreCase("NULL") ? null : Integer.parseInt(row5.Column28)


Your code will fall over if Column28 is null or if it is an empty String or if it is any String that is not "NULL" or not a numeric String. The reason I gave the answer I did is because it covers all bases.

Rilhia Solutions
Six Stars

Re: Int column having Null values

dear @rhall_2_0 we can check all this thing in tmap as well:

 

Relational.ISNULL(column_name) || column_name.isEmpty() || column_name.equalsIgnoreCase("NULL") : null : column_name 

Regards
Aashish
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Fifteen Stars

Re: Int column having Null values

Integer.parseInt(row5.Column28) will return a NumberFormatException which will kill the job if the String passed to it is not a number. There was a reason I supplied the answer that I gave.

Rilhia Solutions
Six Stars

Re: Int column having Null values

in that case, your code also returns incorrect data like if we pass decimal place double/float value will get null only.

 

Note: if we pass 1.9 then it returns numberformatexception and our routine will pass null to the output which is also not correct as per my understanding.

 

Regards
Aashish
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Fifteen Stars

Re: Int column having Null values

Look at the original code. They want an Integer. They are parsing an Integer. As such, the code is fine. I can only work to the requirements I am given. It is correct that it will return false for a decimal. However, if they want to accept Integers and Doubles (the original code will have to change parse an Integer or a Double), they can switch the method around to use Double.parseDouble().

 

Rilhia Solutions