Applying Conditions in Sql Query

One Star

Applying Conditions in Sql Query

Hi All,
I need a help.I have Source db view VW and target table TB.My requirement is to insert the records into target table which satisfies a condition.
In a query, the condition can be expressed like
select * from VW where VW.id not in (select TB.id from TB);
In talend job ,how can we achieve this?Can I use ' Action on data' as 'Insert Ignore' for achieving the same?
Thanks in advance,
Sreedevi
Moderator

Re: Applying Conditions in Sql Query

Hi,
We support the function that inserting the records into target table, which satisfies a condition from Source db target table. But I don't understand your condition.
select * from VW where VW.id not in (select TB.id from TB)

Do you make the condition more detailed. A example will be preferred.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Applying Conditions in Sql Query

Put the query in the tDBtype Input.
Seventeen Stars

Re: Applying Conditions in Sql Query

I guess you mean you want to insert datasets into a table which does exists there?
The simplest way is to set the Action to insert or update.
Unfortunately Talend does not provide a modus of only inserts but without problems if the dataaset violate the uniqueness. You can use a tMap to filter the row which already exists in the target table (main flow from your source and lookup low from your target (joined with an inner join) and the output of tMap is configured as inner join reject. In this way your send only the new datasets to your output table.
One Star

Re: Applying Conditions in Sql Query

Hi All,
I will explain my requirement.I have to insert records into a table,say TB1.For this,I have to select records from a view VW.Schema of view is like productId,ProductName,MFGDt.ProductId is the key column.Schema of Table is like productId,ProductName.The condition for selecting records from VW is that it should not be available in TB1 and MFGDt in the specified range.
Records in VW
----------------
1 aaa 2012-12-08
2 bbb 2012-12-26
3 ccc 2013-01-05
4 ddd 2013-01-10
5 eee 2013-01-22
Records in TB1
--------------
1 aaa
2 bbb
3 ccc
I need to select records from view which are not available in TB1 also coming in a date range like 2013-01-01 to 2013-01-31 .
Selected records
-----------------
4 ddd 2013-01-10
5 eee 2013-01-22
Then insert these records into TB1.So final records in TB1 is
1 aaa
2 bbb
3 ccc
4 ddd
5 eee
How can I do it with Talend?
Thanks,
Sreedevi
One Star

Re: Applying Conditions in Sql Query

Set up a DB-input-component from VW to a tMap. This is your "main".
Set a DB-input-component from TB1 to tMap. This is your "lookup".
Connect the two inputs in tMap using the "Join model" as "inner join".
DBinput-VW --> tMap --> DBoutput-TB1
/
/
DBinput-TB1

Create an output from the tMap to a DB-output component which inserts into TB1.
Set the setting of the output in tMap for "Catch output of inner join reject" to true.
This way only records in VW, which are not in TB1, will be passed out of tMap to TB1.
For a detailed explanation, review the User Guide:
8.2.1.3. How to use Inner Join
8.2.4.4. Lookup Inner Join rejection
One Star

Re: Applying Conditions in Sql Query

Hi All,

Thanks for the help.Its working.
Sreedevi