Joins in Server Job

kab123 · Post by **kab123** » Thu Jan 06, 2005 12:31 pm

Hi ,

I am a guy of Parallel extender...We are asked to do a server job...I am little bit concerned about doing joins (like Inner Join, left Outer...etc.,) how can we specify the type of joins in server job...?
Can anyone pls hint me..?

Thanks

ray.wurlod · Post by **ray.wurlod** » Thu Jan 06, 2005 2:58 pm

The choices in server jobs are much more limited, and boil down to two; either perform the join within the passive stage that is extracting the data, or use reference lookups. The latter is necessary if the data being joined are from disparate databases.

A reference lookup can be fed from any "database" stage or from a hashed file that hash been pre-loaded from data from the probe table.

kab123 · Post by **kab123** » Thu Jan 06, 2005 3:09 pm

As you said we can use a database stage or a hashfile stage, how can we specify what kind of join we need...if i give the condition in constraint box in transformer like lnk1.ColA=lnk2.ColA, it will become innerjoin...how about Outer Joins...

Thanks Ray !!! :D

ray.wurlod · Post by **ray.wurlod** » Thu Jan 06, 2005 3:38 pm

A reference input link is effectively a left outer join - it returns NULL in all columns if the lookup does not succeed.

You can use a constraint on the output link to prevent these rows from being further processed, turning it into an inner join.

Any right outer join can be turned into a left outer join (think on this!).

A single reference input link does not give the capability to perform a full outer join.

If the sources are in the same database, you can do any kind of join in the SQL that extracts the data from that database.

If the sources are two text files, you can do joins - any kind - using the server job's Merge stage (which I neglected to mention earlier).

kab123 · Post by **kab123** » Thu Jan 06, 2005 3:50 pm

Perfect ....

Thank you ver much Ray... :D