topic SQL query to Talend Job JOINS/LOOKUP . in Talend Studio

SQL query to Talend Job JOINS/LOOKUP .

karthik_koneru — Thu, 03 Dec 2015 16:28:40 GMT

Hi Team,
I have a SQL Query which fetches data from two tables(ORDERS AND CUSTOMERS) as follows:
SELECT O.OrderID||'-'||c.CustomerID as ID from ORDERS O,CUSTOMERS c
WHERE NVL(O.OrderName, 'sometext') = NVL(c.CustomerName, 'sometext')
How to check above NVL condition in Talend.

Thanks
KK

Re: SQL query to Talend Job JOINS/LOOKUP .

Anonymous — Thu, 03 Dec 2015 19:22:57 GMT

What do you mean "check"?
You can run this query without any change directly in a tOracleInput component.

"SELECT O.OrderID||'-'||c.CustomerID as ID from ORDERS O,CUSTOMERS c 
WHERE NVL(O.OrderName, 'sometext') = NVL(c.CustomerName, 'sometext')"

Put this query within double quotas like I did and than click on guess schema to get an appropriated schema - but actually, you have only one column...
One question to your query: Do you really want to always join if the OrderName and CustomerName ist null? Looks not as a reliable way.

Re: SQL query to Talend Job JOINS/LOOKUP .

karthik_koneru — Thu, 03 Dec 2015 20:54:21 GMT

Hi Jlolling,
I just made-up those Columns.
Scenario was those two TABLES exist in different database's.
So we cannot use single tOracleInput component to put the sql Query.
My question is, how to implement WHERE Clause Condition NVL(o.Column1, 'sometext') = NVL(c.Column2, 'sometext')
in tmap.
If it is just to compare Main Table condition , with Lookup table.
Then we just use condition in LookTable LabelState column as below
condition: LabelState

I got struck ,how to implement other part of that where clause condition.
NVL(o.Column1, 'sometext') = NVL(c.Column2, 'sometext')
Thanks
KK

Re: SQL query to Talend Job JOINS/LOOKUP .

Anonymous — Thu, 03 Dec 2015 23:59:39 GMT

Simple speaking: you have to prepare the values you need to join different flows before you send them to the tMap.
If you have different database - this implies you have 2 tOracleInput and in this case you have to take care there you do not get null values.