CloverETL - MSSQL 2012 Select statements and JOINs

CloverDX Platform
1

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!

2

Hi mrhong13371,

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

3

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

4

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