PDA

View Full Version : newbie question: how to create outer joins



kettle_anonymous
04-26-2006, 11:25 AM
Hi,


I've started to play with Kettle in the last couple of days and it seems to be the tool I need. However, I can't seem to figure out how to specify an outer join between tables. An inner join seems pretty obvious using Join Rows. Any help is deeply appreciated.



Thanks a lot,



Shuo Wang

MattCasters
04-26-2006, 11:54 AM
Database Join supports this. If you specify "Outer Join" you will get "Outer Join" functionality...
The Database Lookup always outer joins as well, but only returns the first result.


Matt

kettle_anonymous
04-26-2006, 12:57 PM
Hi Matt,


Thanks for the quick reply. I am a little confused about the 'database join' UI. It merely has a text area called SQL. Since I didn't find the particular instruction for this function in the manual, I assume that I need to copy and paste the SQL code that performs the outer join, which looks something like this:



SELECT
TABLEA.X,
TABLEB.Y
FROM TABLEA, TABLEB
WHERE TABLEA.ID = TABLEB.ID(+)



If I merely need to copy and paste this code into the text area, then why do I need to check the 'outer join' checkbox? And also what's the purpose of using 'database join' since I can use the sql script option. Am I missing something here?



Thanks a lot,



Shuo Wang

MattCasters
04-26-2006, 01:11 PM
RTFM:

- spoon-2.2.pdf, page 92, §7.4.32
- Last weeks Kettle Tip on this forum

HTH,
Matt