Removing NULL from different columns in a table

[Former Member]

Hi All,

I am wondering if there is any easy way to remove a LITERAL NULL from many different columns in a target table. I know to use ifthenelse condition for every columns. But there are many columns for my table and i want to do the same for many different jobs too.

So i am searching for a better way to do this. Please help me out.

Thanks in advance,

Kiran.C

[Former Member]

Hi Kiran,

     You can try Validation transform to remove nulls from different columns by writing rule which specifies that the column should not be null

[chethan_lingaraju]

Hello Kiran,

• Option 2
  • Dump your target to flatfile.
  • Search and replace the text NULL with blank in flatfile, Reference.
  • Load flatfile again.
• Option 1
  • Create a script at the end of your job which will have update query for every column.
          You can generate update commands in excel easily.

              

Let me know if it helps.

[Former Member]

Regarding Option 2, you can't "Search and replace the text NULL with blank in flatfile". In the flat file you will find rows like

"value,,value"

not

"value,NULL,value".

[chethan_lingaraju]

Hello Bogdan, from the question "remove a LITERAL NULL", i understood that there is a string text NULL. Are you working on the same issue?

[Former Member]

Check the picture attached by the original poster and you will see that there is no Literal NULL value. He did a print of how it is seen in the database.

[chethan_lingaraju]

Thanks for the clarification

In that case, i see only Option1 to be ideal as it can be done with very less effort.

[Former Member]

Yes Swapnil

[Former Member]

If there is a pattern like 2 or three columns are always NULL, use a Where condition on one column or use nvl() function to convert all NULL to some value say X. Filter all X later in the query transform.

[Former Member]

Arun,

Thanks for the response.

But like as i said there are many columns which have nulls and not all the values in a column are Nulls.

[Former Member]

Some doubts - you said 'LITERAL NULL' does that mean 'NULL' written on the table? Or just NULL value showing on the table. If latter is the case, why do you want to remove it? You cannot remove just the NULL value, the whole record has to be removed for that purpose.

Secondly you need to provide the work flow to find how NULL values are occurring., During the joins or from the source??

[Former Member]

I dont want to remove the records. I just wanted to clear NULL from the records. As I said earlier i think i should try with ifthenelse or NVL.

Thanks for the response Arun.

[Former Member]

Tthe "<Null>" which is a database representation of no value in that column. It can't be REMOVED, but it can be replaced with something else. You can skip rows with <Null> value with help of Validation transform. Please be more clear of what your desire is exactly.

Regards,

Bogdan

[Former Member]

What do you mean by removing the nulls?

Do you want to remove rows having null values?