recursive/hierarchical query

One Star

recursive/hierarchical query

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
One Star

Re: recursive/hierarchical query

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
One Star

Re: recursive/hierarchical query

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?
One Star

Re: recursive/hierarchical query

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.
Employee

Re: recursive/hierarchical query

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.