Transformer is filterring out Nrecords with NULL values

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
ariear
Participant
Posts: 237
Joined: Thu Dec 26, 2002 2:19 pm

Transformer is filterring out Nrecords with NULL values

Post by ariear »

All,

When trying to filter rows with nulls (a transformer after a full-outer join) they're being dropped automatically before they reach the transformer. There even an info msg in the log.
The same work perfectly with a BASIC transformer.

ArieAR
Teej
Participant
Posts: 677
Joined: Fri Aug 08, 2003 9:26 am
Location: USA

Re: Transformer is filterring out Nrecords with NULL values

Post by Teej »

ariear wrote:When trying to filter rows with nulls (a transformer after a full-outer join) they're being dropped automatically before they reach the transformer. There even an info msg in the log.
The same work perfectly with a BASIC transformer.
Null values are considered as the third of the three valid conditions - "True", "False", and "Null".

You will need to capture Null by using "If IsNull() Then Else" statement whenever you do a transformation of a specific field. I am not sure how far we have to go for 7.x yet, but I know for 6.x, we need to handle Null if the column format changes. If the size is different, Null needs to be captured and checked. In some cases, the change in name of the column can require checking for Null.

At least with 7.x, you get an info/warning message. 6.x just drop the record.

Create a reject (dotted) link from the transform stage, to a peek stage, and observe what is being dropped. Then for those fields, address the Null values.

-T.J.
Developer of DataStage Parallel Engine (Orchestrate).
ariear
Participant
Posts: 237
Joined: Thu Dec 26, 2002 2:19 pm

Post by ariear »

You will need to capture Null by using "If IsNull() Then Else" statement whenever you do a transformation of a specific field
What happens is that (I think that the row is dropped just after the Join op before I have a chance to check it (IsNull) in a stage variable/link constraint :?:

I must be doing something wronk - It works if i'm using a BASIC transformer

ArieAR
ariear
Participant
Posts: 237
Joined: Thu Dec 26, 2002 2:19 pm

Post by ariear »

Teej,

I'm still bugged with this one. An outer join (left ,right or full) is supposed to return NULLS. What can be the reason that the rows that had columns with NULLS were in the outer result set be dropped automatically ?

ArieAR
Teej
Participant
Posts: 677
Joined: Fri Aug 08, 2003 9:26 am
Location: USA

Post by Teej »

A full outer join with the Transformer WILL drop records with NULL at least for 6.x. I am not sure of how to get around it, other than the long way (find full inner join, find left outer, find right outer, filter out duplicates, and funnel the results). I asked this question on this forum, and was not able to hear any better solution.

Hopefully, 7.x will be better with the transformer handling full outer join's NULLs.

Now, for your left and right outer join being dropped -- I do not know of any reason why this would happens. Those usually behave very well for us.

-T.J.
Developer of DataStage Parallel Engine (Orchestrate).
ariear
Participant
Posts: 237
Joined: Thu Dec 26, 2002 2:19 pm

Post by ariear »

Right, I put a call with Ascential about it (I'm 7.0 now).
The basic transformer behaves well So I'll use it meanwhile.
Post Reply