NULL Handling Issue

[highpoint]

Hi

I have source decimal column as col1.

1st transformer:

Code: Select all

If isnull(col1) then '' else col1

When i see in the dataset after 1st transformer for the NULL values the col has all 0000000000000

In 2nd Transformer:

Code: Select all

If col= 1  Then 'Y' Else If col= 0  Then 'N' Else ''

Source is a decimal column.
Target is varchar.

But the problem is the output i am getting is 'N' even though the source is NULL.

[anbu]

What is your source?

Try this

Code: Select all

If isnull(col1) Or trim(col1) = '' then '' else col1

[highpoint]

What is your source?

Try this

Code: Select all

If isnull(col1) Or trim(col1) = '' then '' else col1

Nope this doesn't help.

[anbu]

What is your source? File or Table?

[highpoint]

What is your source? File or Table?

Source is a table

[highpoint]

What is your source? File or Table?

Appreciate help on this from professionals

[ray.wurlod]

Decimal data type is always converted to string (including in View Data) with leading and trailing non-significant zeroes. If you don't want them, you have to get rid of them, typically using a Trim() function.

[highpoint]

Decimal data type is always converted to string (including in View Data) with leading and trailing non-significant zeroes. If you don't want them, you have to get rid of them, typically using a Trim() function.

Thanks Ray Wurlod.

But i still dont understand the following:

When my source field is Null. Why i am getting output as 'N' instead of getting ''(Nothing) in my target table after 2 transformers.

1st Transformer:
If isnull(col1) then '' else col1

2nd Transformer
If col= 1 Then 'Y' Else If col= 0 Then 'N' Else ''

Appreciate your reply.

[ray.wurlod]

Do these columns have default values defined at any point in the processing?

[Shaanpriya]

The irrelevant result must be due to blank spaces.

Try using

If NullToEmpty(col1) = trim(col1) then '' else col1

[priyadarshikunal]

seems like you are defaulting empty to a decimal field and thats why it is defaulting it to zero. Try varchar as datatype or default it to a decimal value or you can do intermediate calculations in stage variables.

[highpoint]

Do these columns have default values defined at any point in the processing?

No this column doesnot have any default value.

[ArndW]

If you go to your source stage, double-click on the number at the left of the column or right-mouse click and "edit row" and then see if that column might have a default value set.

[highpoint]

If you go to your source stage, double-click on the number at the left of the column or right-mouse click and "edit row" and then see if that column might have a default value set.

I checked it doesn't have a default value set.

[Sainath.Srinivasan]

Did you check your logs ? Maybe those records are dropped in the transformer due to datatype mismatch.

Also view the source data to ensure that they are NULL. Constraint the flow to about 10 records with different combination to check the result.

Use a new dataset - i.e. create rather than append.