Joining tables: The main one in MySQL and the lookup in Oracle

[Anonymous]

Hi,
I'm reading the data from a table, let's call it Orders. In addition to the primary key, one of the fields is the order date. This table is stored in MySQL, the transactional database with daily operations.

I read a second table, which is the time dimension. This table is already in the datawarehouse that is stored in Oracle. To do the join (INNER JOIN), in tMap, I choose the Orders table as the Main and the Time Dimension as the lookup. I joined the Order Date column with the DateEvent of the time dimension table. In tMap both date fields are masked "dd-MM-yyyy". The join failed and no lines were returned.

On the other hand, if I invert and do the Dimension Time (which is in Oracle) as the main one and the order table (MySQL) as a lookup the join works and returns what was expected. For me this is a little weird because the Orders table is the main one and I was hoping that the first solution above worked. Does anyone have any idea of ​​the problem?

I did another test, I loaded the Orders table from MySQL as a stage table in DW on Oracle db and then I did another job in Talend to test the join. I considered this table (stg) as the main one and Dimension Time as a lookup and then it worked as I was expecting. But I really wanted to join as I explained above. I will appreciate any help.

[manodwhb]

I believe it could be date format issue or may be there is no data for that date.

As part of testing take a single value from order and check for that date you have data in date dimension, run these queries outside of talend, and you will get to know the answer.

[Anonymous]

Thanks for your comments.

Thanks for your answer. But see, in the same job, it works just by making the Time Dimension the main flow and the Orders table the lookup. The result is what I expect, so joining through the Date type columns works and there are orders on that date.

What I would like to do: given an Order find the respective date in the Dimension table to get attributes related to the date.
What's working: Given a date find the Orders for that date.

[manodwhb]

@NelsonTalend,is it left outer join or inner join you have used tMap, and are you taking inner join rejects? 

[Anonymous]

Yes, INNER JOIN. And getting the rejects. See images in attached file

Job_test.png