topic Re: recursive/hierarchical query in Talend Studio

recursive/hierarchical query

Anonymous — Sat, 16 Nov 2024 12:27:11 GMT

Hello,
I've searched, but have not found the definitive answer. Can you tell me, is it possible to create a query or loop in Talend to return the results of a hierarchical query? This is the case where you have a table with a foreign key pointing to itself. It may be N levels deep. The table might look like:
Employee
---------------------------
employee#|manager#
1 2
2 3
3 null
where manager# points back to employee#. Oracle uses a "connect by" syntax for this. I am using JDBC

Re: recursive/hierarchical query

Anonymous — Sat, 14 Jan 2012 03:13:48 GMT

Hi
Try to use tOracleRow and edit sql with "connect by".
Check "Propagate QUERY's recordset" of tOracleRow->Advanced Settings.
Then pass the RecordSet to tParseRecordSet component.
Use tParseRecordSet to retrieve columns.
Regards,
Pedro

Re: recursive/hierarchical query

Anonymous — Mon, 16 Jan 2012 17:02:24 GMT

Sorry, I should have been more clear. I am not using Oracle, that was just an example of what I am trying to do. Is there a way in Talend to do this, either with Talend objects, or with a Loop?

Re: recursive/hierarchical query

Anonymous — Sat, 13 Sep 2014 02:38:13 GMT

I need the same thing. I want to implement the recursive operation in Talend, not the Database. Basically, I want to start at the leaf (employee) and add manager rows until the top node is reached.
For those of you familiar with CTE in MS SQL server, I want to implement the same basic functionality using talend objects.

Re: recursive/hierarchical query

Anonymous — Sun, 05 Oct 2014 22:06:53 GMT

I believe that a tutorial I have put together may help (with a working example for v5.5.1). It emulates the Oracle "Connect By" functionality using Talend components. You can find the tutorial here.