BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

DI FULL OUTER JOIN


 
Search this topic... | Search DI: Designer and Job Design... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Author Message
gpn01
Principal Member
Principal Member



Joined: 30 Nov 2004

Posts: 224
Location: London, UK


flag
PostPosted: Fri Feb 11, 2005 6:14 am 
Post subject: DI FULL OUTER JOIN

Is it possible to do a full outer join on two tables ? DI seens to place a constraint whereby you can only have one table with an outer join and the other as an inner join.

As an example - I have two tables Employee (EMP) and Department (DEPT) with a key of USER_KEY. If I just wanted to return all employees in a Dept I could do it in a simple DI query which would, in SQL look like -

SELECT * FROM EMP E
JOIN DEPT D ON E.USER_KEY = D.USER_KEY

What I want to do though is to return all employees and all departments.

In SQL this would be achieved by

SELECT * FROM EMP E
FULL JOIN DEPT D ON E.USER_KEY = D.USER_KEY

Can the equivalent be done through a DI query ? It seems to only provide a left outer/right outer join (i.e. it'll return all records from one table plus matching records in the second table).

Gary Nuttall
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10592
Location: Germany


flag
PostPosted: Fri Feb 11, 2005 6:21 am 
Post subject: Re: DI FULL OUTER JOIN

I know, I know. Sooner or later we have to make that happen as well.

Today I do a left outer join between both tables, than add another time both tables and a query for performing a right outer join.

So what you end up with is

left hand only plus
joined data

and

joined data plus
right hand only

So you add in either one of the pathes a where clause to get rid of one times the joined data and than merge....

_________________
Werner Daehn
ex Product Manager Data Services at SAP SE

www.rtdi.io
Back to top
gpn01
Principal Member
Principal Member



Joined: 30 Nov 2004

Posts: 224
Location: London, UK


flag
PostPosted: Fri Feb 11, 2005 6:36 am 
Post subject: Re: DI FULL OUTER JOIN

Thanks for the suggestion.

I've logged a request with BO as an enhancement request. Wait and see!
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0333 seconds using 17 queries. (SQL 0.0035 Parse 0.0013 Other 0.0285)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo