Filed value after Full Outer Join ???

Peytot · Post by **Peytot** » Thu Sep 23, 2004 6:30 am

Hello Everybody,

Is anaybody can help me:

I have Two Input Sequential Files:

File A with Field A1 as Key, FieldA2
File B with Field B1 as Key, FiledB2

I do a full Join on FileA.FieldA1 and FileB.FieldB1.

In transform, depending on the source, I would like to put the good key A1 or B1: If A1 exist then C1 = A1 else C1 = B1.

But which value can I test for A1? I try Null, Blanck?

I cannot found the format that Ascential put if I have no data.

Thanks for your help,

Pey

ray.wurlod · Post by **ray.wurlod** » Thu Sep 23, 2004 3:44 pm

What stage type are you using? Given that it's a full outer join, either A1 or B1 might be NULL. Joins are defined the same way as in SQL. If you fail to find a row, NULL is returned. So you need, in your example, to test whether A1 is null.
(Is this really PX? Your description suggests server.)

Peytot · Post by **Peytot** » Fri Sep 24, 2004 11:23 am

Ray,

Yes, It's really PX.

But In the input stages (Sequential Files), my field A1 and B1 are NOT NULL.
After the Join Stage they are always NOT NULL so I think that DataStage puts a default but I don't know which default.

If Already try : IF ISNULL(A1) Then B1 Else A1 but it doesn't working.

Pey

ray.wurlod · Post by **ray.wurlod** » Fri Sep 24, 2004 5:17 pm

Can you post a few rows from each file? Sometimes actually inspecting the data can give inspiration as to what the problem may be.

Is there any conversion (say from "" to NULL) specified in the Format properties as these files are being read?

Perhaps add an extra output link to the Transformer stage that writes a file containing the exact input to the Transformer stage, so you can inspect that. (Does active stage tracing work in PX? I've never tried it. If it does, you could use that rather than modify the job. Limit the run to just a few rows, so that the trace file remains small.)