Page 1 of 1
How to get full outer join using join stage
Posted: Wed Jan 28, 2009 9:00 am
by vijaydev
Hi,
I want to get full outer join results by using three tables.
first i am trying to get full outer join results using two tables.
In the join stage out put/mapping tab it is showing left record and right record how to mapp this to get full outer join results.
Thanks in Advance!
-Vijay
Posted: Wed Jan 28, 2009 9:41 am
by samsuf2002
You can full outer join two tables first using a join stage and then pass the output into another join stage for another full outer join.
There should be many other ways if you search the forum.
For left and right record (might be your link names), it doesn't really matter unless you are mapping the required columns to the output.
Posted: Wed Jan 28, 2009 9:44 am
by Sainath.Srinivasan
Or do it in db itself.
Posted: Wed Jan 28, 2009 5:55 pm
by mk_ds09
If before join stage , in the same job u r using database stage, it will easier for you to modify the query..!
Just in case if your doing some transformations and then using the values from repsective tables then u can use the approach suggested by the post above..
hope this helps
MK
Posted: Wed Jan 28, 2009 7:47 pm
by ray.wurlod
U is one of our posters and is currently working on a server-only project. The second person personal pronoun in English is spelled "you", not "u". And the present tense of the verb "to be" in the second person is spelled "are", not "r".
Please strive for a professional standard of written English on DSXchange. This makes it easier for readers whose first language is not English.
Do you document your actual work using SMS-style abbreviations? Then don't do so here either. DSXchange is not a mobile telephone.
Posted: Wed Jan 28, 2009 10:22 pm
by Alokby
Join Stage in EE will perform full outer join to many tables. Also, one may do the left outer join using SQL. If one has more than two tables to join, I would recommend to use Join Stage in EE over using the database.
Posted: Thu Jan 29, 2009 7:53 am
by vasa_dxx
I commend on Joining the tables in the sourcing query, which is faster & efficient. If a join stage is used, it will burden Perfomance as there will be more backstage processes running.
To design the job more simpler(visually) & improve performance all the possible logics(Business Rules) are tried to incorporate wihin the Sourcing query, wherever used.
Posted: Thu Jan 29, 2009 3:18 pm
by ray.wurlod
Be very careful when making absolute assertions such as these. As always, "it depends".
Posted: Thu Jan 29, 2009 10:33 pm
by vasa_dxx
Thanks Ray. could you please give an example or two where the above assumptions may not hold good/valid. Or did I miscommunicate regarding backstage processes.
Please correct me.
Posted: Thu Jan 29, 2009 11:02 pm
by ray.wurlod
A very simple counter-example is where there is a slow connection to the source database, or where the source database is comparatively very busy compared to the DataStage server.
Re: How to get full outer join using join stage
Posted: Wed May 18, 2011 11:54 pm
by yammanur.dwhcareer
Hi Vijay!
As per my understanding you are struggling at the first join whenever it displayed left record and right record. You can send the both left and right records and put a transformer after the join and write a condition like if the left record is null then populate the right one otherwise left one and then join with the third table and do the same next..
i think it will satisfy your requirements.....