how to do full outer join

mallikharjuna · Post by **mallikharjuna** » Sun Jul 22, 2007 11:30 pm

HiAll,

I have one sequential file and one hash file as sources, how to do full outer join of these with out using oracle or oci stage in server jobs.

Thanks
Malli

ray.wurlod · Post by **ray.wurlod** » Sun Jul 22, 2007 11:32 pm

A full outer join is not possible if a hashed file is involved as one of the sources. Run the hashed file into a sequential file. Then, in a separate job, use a Merge stage to form the full outer join of the two sequential files.

mallikharjuna · Post by **mallikharjuna** » Sun Jul 22, 2007 11:47 pm

can we use merge stage in server jobs?i never used merge stage in server jobs,that's why i am asking this question.

balajisr · Post by **balajisr** » Sun Jul 22, 2007 11:51 pm

mallikharjuna wrote:can we use merge stage in server jobs?i never used merge stage in server jobs,that's why i am asking this question.

Yes. but do not confuse server merge stage with px merge stage. They are different.

mallikharjuna · Post by **mallikharjuna** » Mon Jul 23, 2007 12:24 am

i tried with merge stage in server jobs,but this stage not allowing to giving input links to merge stage.

balajisr · Post by **balajisr** » Mon Jul 23, 2007 12:33 am

mallikharjuna wrote:i tried with merge stage in server jobs,but this stage not allowing to giving input links to merge stage.

It will not accept input links. You can specify the details about the two sequential files within the merge stage. Read the documentation for furthur details.

ray.wurlod · Post by **ray.wurlod** » Mon Jul 23, 2007 1:06 am

That's why you need two separate jobs. The Merge stage (server jobs) reads two sequential files directly (it does not support input links) and forms a join between them based on criteria that you specify.

sachin1 · Post by **sachin1** » Mon Jul 23, 2007 5:40 am

good question, i agree with ray, merge stage will definitely help for outer join.

sachin1 · Post by **sachin1** » Mon Jul 23, 2007 6:24 am

hi ray i have a question like what if full outer join has lot of data, say suppose in GB, will merge hamper the performance.

chulett · Post by **chulett** » Mon Jul 23, 2007 7:14 am

Hamper the performance? It won't be the most performant mechanism choice regardless of join and isn't how I would choose to solve that problem. Me, I'd bulk both files into work tables in my database and do the join there.

jeawin · Post by **jeawin** » Mon Jul 23, 2007 8:20 am

For even greater flexibility you can put data into a hash file or files and then write a pre- or post-transform routine to open the file(s) and merge the data, if necessary into a completely different field (column) format! Just make sure you have sensible key fields.

DSXchange

how to do full outer join

how to do full outer join

Re: how to do full outer join

Re: how to do full outer join

Re: how to do full outer join

Re: how to do full outer join

Re: how to do full outer join

Re: how to do full outer join