SQL query to Talend Job JOINS/LOOKUP .

[karthik_koneru]

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

[Anonymous]

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.

[karthik_koneru]

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

[Anonymous]

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.