DSXchange

All,

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

Where and how it is determined ?

ArieAR

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.

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

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!

-T.J.

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

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.

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:
For example:
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.

Great Teej :D

just use the modify stage instead of transformer stage.I even came across the same problem .
vasu

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