CloverETL - MSSQL 2012 Select statements and JOINs

mrhong13371

• November 18, 2016 00:00

• Answered

Follow

Hey there,
I have an SQL statement which I want to "translate" into CloverETL. So basically I have an SQL statement similar to:

Select 
	Professoren.persnr AS [Prof PersNr]
INTO
	[Prof - Assistent]
FROM
	Professoren

LEFT JOIN 	Assistenten
ON 			Boss

So I am a little bit stuck here. I tried to use official user guide and followed following approach:
1. Created DBInputTable containing SQL query:

SELECT * from professoren

2. Creating DBJoin with Join key persnr and SQL query

SELECT * from assistenten WHERE boss = ?

(Assistenten.boss is foreign key from Professoren.persnr)

3. Creating incoming edge into DBJoin w/ metadata from professoren containing only persnr
Creating outgoing edge from DBJoin w/ metadata from professoring containing only persnr

Note: I used same DB connection for DBInputTable and DBJoin. also have a look at database schema.


Thank in advance for any hint! I am really stuck on this one!

• uni schema.PNG

• 

  Pedro Vazquez Rosario

  • November 21, 2016 16:10

  0

  Comment actions Permalink

  Hi mrhong13371,
  
  Could you please tell me what the expected output of this use case will be?
• 

  mrhong13371

  • November 22, 2016 08:31

  0

  Comment actions Permalink

  HI vazquezrosariop,
  
  thanks for your reply! This is just an generic example. At the end of the day, I want to join two different tables on one key and I do not understand how to realize this example w/ CloverETL.
  
  Thank you for your time!
  
  
  Best regards,
  Shun Long
• 

  Pedro Vazquez Rosario

  • November 22, 2016 15:37

  0

  Comment actions Permalink

  Hi,
  
  You seem to be on the correct track, you need to access the Transformation editor in the DBJoin to map the input values to the output values from both databases.
  
  Another option to your use case would be to add a second DBInputTable containing SQL query:
  
  

  Select * from assistenten

  
  
  and use a ExtMergeJoin to join the two input streams by using the appropriate Join Key. Finally, use the Transformation editor to map the input metadata to the correct output metadata.
  
  I have taken the liberty of attaching a project with both use case implemented.
  
  DatabaseExample.zip

  • DatabaseExample.zip

Please sign in to leave a comment.