Rejecting Null records

[Abhi700]

I had a situation where i have to reject Null records .
The Null records may be with spaces in one row & not having spaces at other.
I had used len(stripwhitespaces(col_name))=0 or isnull(col_name)
but it is rejecting only the column which is having spaces.
So i need to reject both columns which are null's either empty spaces or null value.

Please Suggest!!!!!

[jwiles]

Perhaps something along the lines of

len(trim(NullToEmpty(column)))

Regards,

[chulett]

If this "len(stripwhitespaces(col_name))=0 or isnull(col_name)" only rejects spaces then you don't have any nulls. What is your source?

[Abhi700]

My source contains Empty spaces & Nulls in the primary key Columns.
So I have to Handle both to reject.

[Ravi.K]

From your initial post what is meant by "null". whether it is "Empty column with out any space" or "null".

if it is really "null "then your initial logic should work. If not then try with following derivation.

Left(Trim(NullToEmpty(column)),1)=''

[chulett]

Abhi700 - You seem to have missed my point... and still haven't clarified your source - flat file? Database table? Something else entirely?

[Abhi700]

My source file is Sequntial file.
And it contains empty spaces & without spaces i.e delimiter without spaces.
I have to reject both of them to rejected data.
For e.g " ,,Abhi,20100101,"
I have to rejecr both the columns

[jwiles]

This being the case, you are calling the empty columns (those without spaces) the Nulls? Do your input options convert empty strings to NULLs?

If your columns are set as Nullable=Yes, then check for Len(Trim(NullToEmpty(inputcolumn)))=0. If they are set as Nullable=No, then Len(Trim(inputcolumn))=0 should be sufficient. An variation of these would be: Trim(NullToEmpty(inputcolumn))="", or Trim(inputcolumn)="", respectively.

Regards,

[Abhi700]

When i am using Trim(Input Col)="" then its working when input col has atleast one empty space.
Actually source row is " ,,abhi ,20100115,".
Then such record should be rejected.
When I am using Trim(Input Col)="".I tried with Trim(NullT0Empty(InputCol))="".
In both the cases record is dropping.
I need the reject to be rejected.

[Shruthi]

Try this.
StageVar:
If StripWhiteSpace(NullToEmpty(Input Col))<>'' Then If IsNotNull(Input Col) Then 0 Else 0 Else InputCol

Reject in Transformer where StageVar=0

Hope this works!

[Abhi700]

StageVar:
If StripWhiteSpace(NullToEmpty(Input Col))<>'' Then If IsNotNull(Input Col) Then 0 Else 0 Else InputCol

Reject in Transformer where StageVar=0

I tried with this.
But my Issue ha not resolved.
As specified in the above error my seq file record is as follows
" ,,Abhi,20100105"
I have to move it to reject link.

[Shruthi]

Do a view of this sequential file in Seq File Stage. Are these values getting read as NULL or Blanks.

What is the condistion you have given in Transformer.

Can you post your code here.

[Ravi.K]

Try the below logic once.

StageVar:
If Left(Trim(NullToEmpty(column)),1)='' Then '0' Else '1'

Reject in Transformer where StageVar=0

It may help you.

[Abhi700]

Do a view of this sequential file in Seq File Stage. Are these values getting read as NULL or Blanks.

What is the condistion you have given in Transformer.

Can you post your code here.

I am getting Nulls if there are no empty spaces i.e delimiter. If there are any empty spaces then it shows as blanks.

[Abhi700]

StageVar:
If Left(Trim(NullToEmpty(column)),1)='' Then '0' Else '1'

Reject in Transformer where StageVar=0

Problem Solved

Thanks to every body