HandleNull

kksr · Post by **kksr** » Thu Apr 13, 2006 3:12 am

Hi..

HandleNull through Transform Stage is possible in DS 7.1 or not?

thompsonp · Post by **thompsonp** » Thu Apr 13, 2006 4:24 am

In the derivation section of the transformer right click and choose Function -> Null Handling

You will see there are several choices for converting a Null to something else.

cwong · Post by **cwong** » Sat Apr 15, 2006 8:33 am

DataStage EE 7.5x2
Server OS: Window 2003

For Null Handling, how are you tackling DATE ?

When the Input and Output column is delcared as DATE type, is having trouble to use the available NULL Handling function on dealing with Null date column from input and mapping to an output column.

The input data row with Null date got dropped as stated by PX.

Attempted to use Null handling around the input column, but for example:
NullToEmpty ==> compile time error
NullToValue( InDate, '1900-01-01' ) ==> No good either
IF IsNull(InDate) Then ... Else .... ==> No effect either

Have I missed or mixed up a bit ? or there is a bug ??

Thanks in advance for any tips to get around this.

ray.wurlod · Post by **ray.wurlod** » Sat Apr 15, 2006 3:26 pm

Try choosing the input column name from the expression editor so that your input column name is fully qualified with the input link name.

cwong · Post by **cwong** » Sun Apr 16, 2006 2:45 pm

Hi Ray,

Yes, the input column was qualified with the link name.
The example was just a short-hand typing to illustrate the issue.

IF IsNull( SrcInputData.LAST_SNAP_DT ) Then SetNull() Else SrcInputData.LAST_SNAP_DT

Is this what you meant ?

This transformation works for DataSet except Sequential file.

Please advise ...

Thanks.

ray.wurlod · Post by **ray.wurlod** » Sun Apr 16, 2006 3:07 pm

Since there is a virtual Data Set between your Transformer stage and your Sequential File stage (you can see this in the generated OSH), if there is a bug or a design problem it's within the Sequential File stage. Can you thoroughly check the metadata in the Sequential File stage and its input link, even to the extent of opening the Edit Row dialog for the LAST_SNAP_DT column, to see if there's anything there that might cause this behaviour? Add a Peek stage between your Transformer stage and your Sequential File stage to capture what's leaving the Transformer stage.

cwong · Post by **cwong** » Sun Apr 16, 2006 4:04 pm

Okay, I just paid to get my membership upgraded so that I could see the crucial portion of your message ...

khaja · Post by **khaja** » Mon Apr 17, 2006 12:02 am

Hi can i get the reply from Ray ..bcoz i have the same issue a long back..but some hw i managed

ray.wurlod · Post by **ray.wurlod** » Mon Apr 17, 2006 12:35 am

Welcome aboard. :D

The five most experienced posters on this site are "premium posters". What that means, in effect, is that their replies are considered to have some value, and a small charge (less than $1 per week) is levied so that you can read the premium posts. This revenue helps towards the costs of running this site.

You would pay considerably more to have even one question answered on a commercial basis.

Of course, you can choose to direct all your questions to your support provider (paying maintenance entitles you to do so) but you will find a wealth of practical experience here - not just from the premium posters - that your support provider may not have. On the other hand, your support provider is probably more beneficial in getting bugs resolved.

This post contains additional information about handling NULL in the Modify stage.

thompsonp · Post by **thompsonp** » Tue Apr 18, 2006 2:57 am

Your example code is setting the ouptut value to null if the input is null, otherwise it sets it to itself. What is the point of that?

If you are then writing this to a sequential file you need to handle the null differently. Remember there is no such thing as a null in a sequential file. You have to write something to the file, either a character that will represent your null values or just an empty string.

cwong · Post by **cwong** » Tue Apr 18, 2006 8:47 pm

Yes, you are correct about the target column needing to be a NON-null value.

My question is more about Null handling for a DATE type column.

Attempted to use Null handling around the input column, but for example:
NullToEmpty ==> compile time error
NullToValue( InDate, '1900-01-01' ) ==> No good either

emm ... probably should try something like this:
NullToValue( InDate, StringToDate('1900-01-01') )

Will try this out tomorrow to see ... or if you have any idea or experience in handling this ...

BTW, the
IF IsNull(InDate) Then SetNull() Else InDate
is used to fulfill the Null Handling requirement to avoid the data row with Null value got dropped. Technically, there is no effective in the end result.

ray.wurlod · Post by **ray.wurlod** » Tue Apr 18, 2006 8:51 pm

Whatever replacement date value you give must correspond exactly to the date picture currectly specified for the job, unless you override that with a formatting option (for example in the handle_null() specification in a Modify stage).

kumar_s · Post by **kumar_s** » Wed Apr 19, 2006 6:59 am

Hi,
You can try to feed the same using the Nullable Field value in the sequential stage. Or the Default value option in the field properties to give your default vaule in the date column.