Achieving Right Outer join in a Server Job

[yaminids]

Hello friends,

Can I perform Right Outer join in a Server job.

Thank you
-Yamini

[mhester]

Can you be more specific regarding your request? Do you intend to accomplish this with a hash lookup or user written SQL in a stage?

[yaminids]

Hi Micheal,

I intend to perform the right outer join using a Hash file stage.

Can you help me with this

Thanks in advance
-Yamini

[kduke]

All hash file lookups are outer joins because DataStage does not care if it finds a hash record or not.

[mhester]

Identify the columns or metadata on the target link with all of the columns you want. Perform a lookup against the hash file and interrogate if the lookup was successful by checking LKUP_LINK.NOTFOUND. If the lookup was successful then the target link would be populated with the values from the lookup file. If the lookup was not successful then you would assign @NULL to these columns.

Do not constrain on whether the lookup was successful or not because you want every row to flow through, but with null values for some columns when the lookup failed.

Does this seem reasonable?

Regards,

[ray.wurlod]

All hashed file lookups are LEFT outer joins (the row returned from the reference input link contains NULL in every column if the key is not matched).

It is provable that any RIGHT outer join can be rewritten as a LEFT outer join, but that word "rewritten" says it all; your job design would require radical change.

[yaminids]

Hi Micheal,

I tried the logic you specified but still getting the result of Left outer join. Can you explain me clearly how to get all rows from the Hash file.

Ray, Can you explain me the changes I have to make to the job to achieve right outer join

Thank you
-Yamini

[vmcburney]

If you want to do a right outer join then you have to reverse your input and lookup links to turn it into a left outer join. This turns your hash file into an input stream instead of a reference lookup. It means turning your input stream into a lookup. If it is a database table you can do a direct reference lookup, otherwise you may have to load it into a hash file.

Enterprise Edition parallel jobs give you a a few choices for right outer joins but server edition gives you no choices.

[mhester]

I was not clear in my post, but you do need to redesign your job as Ray and others posted. Your source now needs to be the lookup and the lookup should now be the source. If your job is not too terribly complicated then I would think that this should be quite easy to accomplish.

[yaminids]

Hi Micheal,

The main problem for me to implement your logic is the key columns in the Hash file. If I change the Hash file to Sequential file and vice versa, I run into the scenario where the Hash file does not have all the columns of the Sequential file

For example:
This is the job design if I make the above change.


HASHFILE
|
|
|
Transformer
|
|
|
SOURCE_FILE------------->TARGET_FILE


For example, the key columns of the Hash file are:
Name, Dept, SID

The columns in the SOURCE_FILE are
Name, SID

The columns in the TARGET_FILE are
Name, Dept, SID

Is there a way to perform look up with the matching columns?

Thank you
-Yamini

[mhester]

Is the SID unique? If not and you require all three (3) columns for your key then you have an issue and one that would be present regardless of tool used to join the tables. If SID is unique then you could build the Hash with the SID as the key and Name and Dept as fields or columns.

You need to have some common key between the source stream and the lookup for this to work using a Hash lookup no matter which file is being used as input.

Regards,