Getting default values instead of nulls in Join & lookup

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

Getting default values instead of nulls in Join & lookup

Post by ariear »

All,

All my jobs are returning default values instead of NULLS when using Join or Lookup operators.

Where and how it is determined ?

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

Re: Getting default values instead of nulls in Join & lo

Post by Teej »

Now you've pitiqued my curiousity, along with the other thread noting that they're getting a value instead of NULL. Obviously, something has been set somewhere, perhaps in Administrator?

If you find the answer, let us know -- there's more than one person who are very interested in knowing the cause.

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

Post by ariear »

Well, It seems that when a join/lookup ops is being used (didn't check merge yet) it's not enough that your result set column definition will have nullable columns but the INPUTS as well (Placed a call with ascential). It was deduced from a demo job that I got. There a non-nullable column was changed to nullable (using COPY op) and was checked for null in case of a left-outer join. What I think is that PX is keeping a source to target column attributes consistency.

And i found out something else. my next thread.

By the way teej I took your advise seriously and I'm trying to avoid transformers whenever I can.

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

Post by Teej »

ariear wrote:By the way teej I took your advise seriously and I'm trying to avoid transformers whenever I can.
Woah woah... I did not mean to imply that transformers should be avoided at ALL costs.

What I am trying to say here is: Be prudent when using any stage. It does not make sense to do a join when a lookup stage can do. It does not make sense to have funnel stages to one Oracle output, if it means you have to do both insert and updates when the alternative is having two stages, one doing pure load, and another doing updates.

It does not make sense to use the transform stage to modify field names, when a modify stage would do. Keep your design simple, and clear. This is true for all development language, not just DataStage.

The worst thing that can happen during ANY type of development is to say, "Do not do it because someone else said so." There are values for everything that is provided with the PX engine. You just need to figure out the cost and benefits, and decide for yourself which is better for your needs.

That is what experience provides everyone -- awareness of the tool and its true value for the entire process.

Yes, Virginia, there really can be some good in using goto sometimes! :mrgreen:

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

Post by ariear »

Well not at ALL COST of course and you put it right - use a modify to change datatype/column names etc...
I even tried to use a filter stage instead of a transformer but I can't find the syntax how to filter NULL values in a column (simple is it ?)

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

Post by Teej »

ariear wrote:Well not at ALL COST of course and you put it right - use a modify to change datatype/column names etc...
I even tried to use a filter stage instead of a transformer but I can't find the syntax how to filter NULL values in a column (simple is it ?)

ArieAR
Ah, I looked at the help screen for that stage (click on "Help"), then clicked "this link". As you can see, this is focused specifically on constraints. It does not have any other abilities.

Modify Stage, on the other hand might be able to do this. However, I am struggling on the NullToZero() function (for some reason, the docs and the code is not identifying with each other). Will get back to you on this.

-T.J.
Developer of DataStage Parallel Engine (Orchestrate).
Teej
Participant
Posts: 677
Joined: Fri Aug 08, 2003 9:26 am
Location: USA

Post by Teej »

Bingo!

You _MUST_ use the OSH documentation for your Modify Stage. "parjdev.pdf" is so wrong it's not funny. It was probably decided during the design process that Modify Stage use the original OSH code instead of the Transformer-equivalent functions.

You need to do this in the Modify Stage:

Code: Select all

[output field name].[type] = handle_null([input field], [new value to replace NULL])
For example:

Code: Select all

newrow = handle_null(value, -1)
There is no specification of the input link and output link names here. As I said, pure OSH code. Documentation you need is OperatorsRef.pdf, page 13 32 to 13 33.

If you do not have the OSH docs, ask your Ascential Support Rep to send you a copy.

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

Post by ariear »

Great Teej :D
vasu77
Participant
Posts: 10
Joined: Mon Jan 19, 2004 11:39 am

Post by vasu77 »

just use the modify stage instead of transformer stage.I even came across the same problem .
vasu
bigpoppa
Participant
Posts: 190
Joined: Fri Feb 28, 2003 11:39 am

Getting default values instead of nulls in Join & lookup

Post by bigpoppa »

T.J.,

"parjdev.pdf" is wrong? ASCL put out a bad doc? It couldn't be.

I hope you are kind enough to let ASCL know what its errors are in parjdev.pdf. You will be doing a service to the community.

-BP
Post Reply