full outer join

oldforum

• July 16, 2007 00:00

• Answered

Follow

Is there a way to do a full outer join, without joining the files twice? Am I missing something -- I only saw left outer.

• 

  oldforum

  • July 16, 2007 08:23

  0

  Comment actions Permalink

  While we are in this topic, is it possible to have several out ports, 3 in particular: (if A and B are two files)
  outport0: results of inner join between A and B
  outport1: records in A but not in B (outer join on B)
  outport2: records in B but not A (outer join on A)
  
  … assuming join on some key.
  
  Above is "nirvana" for data warehousing situations, reducing considerable processing.
  
  Thanks
  Akhil
• 

  oldforum

  • July 16, 2007 08:23

  0

  Comment actions Permalink

  That'll be awesome!
  
  In Data Warehousing situations where one routinely compares a data set (say employee) from one period to another to get inserts (new employees)/updates (changed employees) and deletes (deleted employees) , a full outer join can do all 3 in one shot. Very useful for doing type-1 and 2 versioning.
  
  This way, one needs to join the 2 data sets only ones.
  
  By the way, cloverEtl rocks! Great Job!!!
  
  Do you have a target feature list/date for the next release?
  
  Also, I am doing some bench marks comparing clover tasks with similar oracle/mySql tasks. I'll publish it when I am done.
  
  Best
  Akhil
• 

  oldforum

  • July 16, 2007 08:23

  0

  Comment actions Permalink

  Hmm,
  
  I have never seen such component in "big" ETL tools, but this is really easy one. I have finished the outer join option in MERGE_JOIN and this is just simple modification - based on matching driver&slave records send it to one of the three ports.
  
  David.
• 

  oldforum

  • July 16, 2007 08:23

  0

  Comment actions Permalink

  Hi!
  
  Your observation is correct. There is no full-outer-join as of version 1.1.4.
  I will try to include it in version 1.1.5 as this is a simple addition. Only I have never thought of using it.
  
  David.
• 

  oldforum

  • July 16, 2007 08:23

  0

  Comment actions Permalink

  AbInitio is one such tool, that's where I was exposed to this component. This tool is a market leader in handling high volume ETL.

Please sign in to leave a comment.