# Question On HASH JOIN

**URL:** <https://forum.cloverdx.com/t/question-on-hash-join/465>\
**Category:** CloverDX Platform\
**Created:** [January 22, 2009, 12:00am UTC](https://forum.cloverdx.com/t/question-on-hash-join/465 "2009-01-22T00:00:00Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![kzar](https://avatars.discourse-cdn.com/v4/letter/k/7ab992/32.png) [@kzar](https://forum.cloverdx.com/u/kzar)\
**Post date:** [January 22, 2009, 12:00am UTC](https://forum.cloverdx.com/t/question-on-hash-join/465/1 "2009-01-22T00:00:00Z")

</div>

Hi ,

I am new to clover etl and I am making changes to an existing clover ETL application and here is where I was getting stuck :

I have two table A and B and I am extracting columns from the database A to the Datamart table A\_DM using HASH JOIN :

${out.0.EMP\_PHONE\_NUM} = ${in.1.EMP\_PHONE};

How does the HASH JOIN know how to join the 2 tables , what I mean by that is does the type HASH JOIN know the primary keys , foreign keys ? If so how does it know ?

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [January 26, 2009, 9:49am UTC](https://forum.cloverdx.com/t/question-on-hash-join/465/2 "2009-01-26T09:49:40Z")

</div>

Hello,  
you have to define keys in your Joiner: attribute _joinKey_ should look like _$EmployeeID=$EmployeeID;#_, where the left side defines primary key and the right side - foreign key. When the key name is in both cases the same, you can use only _joinKey=EmployeeID_ like in your example. For complete documentation see [HashJoin component](http://wiki.clovergui.net/doku.php?id=components:joiners#hashjoin%5Fext); checking [HashJoin example](http://wiki.clovergui.net/doku.php?id=simple%5Fexamples#hash%5Fjoin%5Fexample) could be useful too.

---

<div class="post-metadata">

**Author:** ![kzar](https://avatars.discourse-cdn.com/v4/letter/k/7ab992/32.png) [@kzar](https://forum.cloverdx.com/u/kzar)\
**Post date:** [January 28, 2009, 6:50am UTC](https://forum.cloverdx.com/t/question-on-hash-join/465/3 "2009-01-28T06:50:42Z")

</div>

Thanks Agata , that did help me !!

Further more the issue I am dealing with is is that I want to Join three tables and all the three tables do not have any one key in common , for example :

Tables :

1. Employee : Key : Employee\_ID
2. Customer : Key : Employee\_ID, ORDER\_ID
3. Order : Key : Order\_ID

Can i do some thing like this which would join these 3 tables :

Does this provider a proper join since only one table has common keys, in this case that is the “Customer” table.

If not I was planning on Joining the Employee & Customer and then take the result of this join and join it with Order ?

Second part of the question :  
I want to use the following type DB\_INPUT\_TABLE based on the previous join :

\<Node id=“UPD\_FACT\_TABLE”  
type=“DB\_INPUT\_TABLE”  
commit=“12345”  
batchMode=“Y”  
dbConnection=“ORACLE\_DATAMART”  
dbColumns=“ORDER\_ID:EMPLOYEE\_ID”  
sourceTable=“MY\_DB.TRACK\_ORDER”  
sqlQuery="select TRACKING\_TYPE from TRACK\_ORDER where ORDER\_ID = ? and EMPLOYEED\_ID = ? /\>  
  
How does the ‘?’ know that they have to be replaced buy ORDER\_ID and EMPLOYEE\_ID from the previous HASH\_JOIN ??

Please let me know, I am trying various options and unable to get any thing working.

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [January 28, 2009, 8:20am UTC](https://forum.cloverdx.com/t/question-on-hash-join/465/4 "2009-01-28T08:20:43Z")

</div>

Hello,  
your key for three tables is proper or you can write it more explicitly: _joinKey=$Employee\_ID=$Employee\_ID;#$ORDER\_ID=$Order\_ID;#"_. For joining data from database you can’t use DBInputTable - this only for reading data; for joining data use [DBJoin component](http://wiki.clovergui.net/doku.php?id=components:joiners#dbjoin). See also examples: [DBJoin example](http://wiki.clovergui.net/doku.php?id=database%5Fexamples#dbjoin%5Fexample) and [DBUnloadParametrized example](http://wiki.clovergui.net/examples/graphDBUnloadParametrized.grf).[/i]

---

<div class="post-metadata">

**Author:** ![kzar](https://avatars.discourse-cdn.com/v4/letter/k/7ab992/32.png) [@kzar](https://forum.cloverdx.com/u/kzar)\
**Post date:** [January 28, 2009, 11:08pm UTC](https://forum.cloverdx.com/t/question-on-hash-join/465/5 "2009-01-28T23:08:18Z")

</div>

Hi Agata,

I am using version 2.1 and it only offers HASH\_JOIN and it does not support 3 way join. so I tried doing some thing like this.

THE EDGE looks like this

where the superset involves the metadata of all the three tables : employee,customer,order.

The issue is JOIN\_TABLE\_1\_&\_2-WITH\_3 is refusing to accept the join key: ORDER\_ID=Order\_ID and throws a message that

java.lang.RuntimeException: Field name specified as a key doesn’t exist: ORDER\_ID=Order\_ID  
at org.jetel.data.RecordKey.init(RecordKey.java:117)  
at org.jetel.component.HashJoin.init(HashJoin.java:299)  
at org.jetel.graph.Phase.init(Phase.java:150)

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [January 29, 2009, 8:15am UTC](https://forum.cloverdx.com/t/question-on-hash-join/465/6 "2009-01-29T08:15:07Z")

</div>

In such old version use _joinKey_ attribute for primary key and _slaveOverrideKey_ for foreign key. By the way why do use so old unsupported version?

---

<div class="post-metadata">

**Author:** ![kzar](https://avatars.discourse-cdn.com/v4/letter/k/7ab992/32.png) [@kzar](https://forum.cloverdx.com/u/kzar)\
**Post date:** [January 29, 2009, 8:55pm UTC](https://forum.cloverdx.com/t/question-on-hash-join/465/7 "2009-01-29T20:55:22Z")

</div>

I am working on an existing application, and I have a very specific change and the time line doe not allow me to change the version and retest the entire application. That is the reason I am stuck to the older version.

---

<div class="post-metadata">

**Author:** ![kzar](https://avatars.discourse-cdn.com/v4/letter/k/7ab992/32.png) [@kzar](https://forum.cloverdx.com/u/kzar)\
**Post date:** [January 30, 2009, 12:13am UTC](https://forum.cloverdx.com/t/question-on-hash-join/465/8 "2009-01-30T00:13:46Z")

</div>

Hi Agata,

I did a work around the joins, what I did was:

1. created a View in the data base which select all the columns.

2. Call the recordTransformDefaults guy and the I ma trying to insert.

But along the path when I reach a particular point it throws he this error :

component.DBOutputTable (run:395) - RESULT\_ERROR:  
java.sql.SQLException: Incompatible Clover & JDBC field types Clover type: string

I checked the metadata for the particular column its of the type string in both the places i.e from and to coloumn.

Any Ideas ?

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [January 30, 2009, 8:08am UTC](https://forum.cloverdx.com/t/question-on-hash-join/465/9 "2009-01-30T08:08:07Z")

</div>

Try to extract metadata from db (in GUI or by AnalyzeDB) and then check fields’ types.