You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Vrin26 <di...@in.ibm.com> on 2009/10/09 13:52:19 UTC
Recursive query on common table
Hi,
I need to do a recursive query on a single derby database table.
In Derby database documents it is mentioned that this derby doesn't support
recursive queries.
Is there any other work around to handle this scenario?
Thanks
--
View this message in context: http://www.nabble.com/Recursive-query-on-common-table-tp25819772p25819772.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Recursive query on common table
Posted by Vrin26 <di...@in.ibm.com>.
Thanks for the Code.
I will try modifying and implement in the way i need.
Vrin
Sylvain Leroux wrote:
>
> Hi,
>
> Here is a quick and dirty class I wrote yesterday in order to experiment
> with
> the (very useful) table function wrappers provided by Rick.
>
> This function will return the list of all children from a given node
> (through
> the relation Zones.ref <--> Zones.parent).
> The table name and the column that serves as reference to the parent node
> are
> all hard coded. But I think you might be found that useful.
>
> Please note that in it current implementation, it could be used both for
> trees
> and graphs containing cycles.
>
> Hope this helps,
> Sylvain.
>
> Vrin26 a écrit :
>>
>> Hi,
>> Can any one help me with an idea, how I can do a recursion for this
>> issue.
>> Thanks.
>>
>>
>> Rick Hillegas-2 wrote:
>>> Vrin26 wrote:
>>>> Hi,
>>>>
>>>> I need to do a recursive query on a single derby database table.
>>>> In Derby database documents it is mentioned that this derby doesn't
>>>> support
>>>> recursive queries.
>>>> Is there any other work around to handle this scenario?
>>>>
>>>> Thanks
>>>>
>>>>
>>> You can push the recursion into a table function or into a database
>>> procedure which returns a result set. The table function documentation
>>> talks about wrapping external data sources, but you can also use table
>>> functions to wrap complicated processing inside Derby itself. See
>>> http://db.apache.org/derby/docs/10.5/devguide/devguide-single.html#cdevspecialtabfuncs
>>> The CREATE PROCEDURE statement is documented here:
>>> http://db.apache.org/derby/docs/10.5/ref/ref-single.html#crefsqlj95081
>>>
>>> Hope this helps,
>>> -Rick
>>>
>>>
>>
>
>
> --
> Website: http://www.chicoree.fr
>
> package fr.chicoree.derby;
>
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Statement;
> import java.util.HashSet;
> import java.util.Set;
>
> import sun.javadb.vti.core.EnumeratorTableFunction;
> /*
> Works on this table:
> CREATE TABLE Zones (
> -- Unique ID
> id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
>
> -- Reference to the parent
> parent INT REFERENCES Zones(id),
>
> -- désignation
> designation CHAR(80) NOT NULL
> );
> Usage:
> CREATE FUNCTION AllChildren(rootNode INT) RETURNS TABLE (Children INT)
> LANGUAGE java
> PARAMETER STYLE DERBY_JDBC_RESULT_SET
> READS SQL DATA
> EXTERNAL NAME 'fr.chicoree.derby.AllChildren.allChildren'
> ;
>
> -- Get all children of node 2:
> SELECT * FROM TABLE (AllChildren(2) ) AS T;
>
> */
> public class AllChildren extends EnumeratorTableFunction {
> static final String[] columns = { "Children" };
>
> public AllChildren(int rootNode) throws SQLException {
> super(columns);
>
> Set<Integer> subTree = new HashSet<Integer>();
> Set<Integer> parentSet = new HashSet<Integer>();
> parentSet.add(rootNode);
>
> Connection conn = DriverManager.getConnection(
> "jdbc:default:connection" );
> Statement stmt = conn.createStatement();
> final String query = "SELECT Children.id " +
> " FROM Zones AS Children " +
> " INNER JOIN Zones AS Parent " +
> " ON Parent.id = Children.parent" +
> " WHERE Parent.id in (%s)";
>
> int subTreeSize = subTree.size();
> int subTreePrevSize;
> do {
> StringBuilder args = new StringBuilder();
> String glue = "";
> for(Integer node : parentSet) {
> args.append(glue);
> args.append(node);
> glue = ",";
> }
>
> // System.err.println(String.format(query, args.toString()));
> ResultSet rs = stmt.executeQuery(String.format(query,
> args.toString()));
>
> parentSet = new HashSet<Integer>();
> while(rs.next()) {
> Integer child = rs.getInt(1);
> subTree.add(child);
> parentSet.add(child);
> }
>
> subTreePrevSize = subTreeSize;
> subTreeSize = subTree.size();
> } while(subTreePrevSize < subTreeSize);
>
> setEnumeration(subTree);
> }
>
> public static ResultSet allChildren(int rootNode) throws SQLException
> { return new AllChildren(rootNode); }
>
> @Override
> public String[] makeRow(Object node) throws SQLException {
> return new String[] { node.toString() };
> }
> }
>
>
--
View this message in context: http://www.nabble.com/Recursive-query-on-common-table-tp25819772p26021759.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Recursive query on common table
Posted by Sylvain Leroux <sl...@wanadoo.fr>.
Hi,
Here is a quick and dirty class I wrote yesterday in order to experiment with
the (very useful) table function wrappers provided by Rick.
This function will return the list of all children from a given node (through
the relation Zones.ref <--> Zones.parent).
The table name and the column that serves as reference to the parent node are
all hard coded. But I think you might be found that useful.
Please note that in it current implementation, it could be used both for trees
and graphs containing cycles.
Hope this helps,
Sylvain.
Vrin26 a écrit :
>
> Hi,
> Can any one help me with an idea, how I can do a recursion for this issue.
> Thanks.
>
>
> Rick Hillegas-2 wrote:
>> Vrin26 wrote:
>>> Hi,
>>>
>>> I need to do a recursive query on a single derby database table.
>>> In Derby database documents it is mentioned that this derby doesn't
>>> support
>>> recursive queries.
>>> Is there any other work around to handle this scenario?
>>>
>>> Thanks
>>>
>>>
>> You can push the recursion into a table function or into a database
>> procedure which returns a result set. The table function documentation
>> talks about wrapping external data sources, but you can also use table
>> functions to wrap complicated processing inside Derby itself. See
>> http://db.apache.org/derby/docs/10.5/devguide/devguide-single.html#cdevspecialtabfuncs
>> The CREATE PROCEDURE statement is documented here:
>> http://db.apache.org/derby/docs/10.5/ref/ref-single.html#crefsqlj95081
>>
>> Hope this helps,
>> -Rick
>>
>>
>
--
Website: http://www.chicoree.fr
Re: Recursive query on common table
Posted by Vrin26 <di...@in.ibm.com>.
Hi,
Can any one help me with an idea, how I can do a recursion for this issue.
Thanks.
Rick Hillegas-2 wrote:
>
> Vrin26 wrote:
>> Hi,
>>
>> I need to do a recursive query on a single derby database table.
>> In Derby database documents it is mentioned that this derby doesn't
>> support
>> recursive queries.
>> Is there any other work around to handle this scenario?
>>
>> Thanks
>>
>>
> You can push the recursion into a table function or into a database
> procedure which returns a result set. The table function documentation
> talks about wrapping external data sources, but you can also use table
> functions to wrap complicated processing inside Derby itself. See
> http://db.apache.org/derby/docs/10.5/devguide/devguide-single.html#cdevspecialtabfuncs
> The CREATE PROCEDURE statement is documented here:
> http://db.apache.org/derby/docs/10.5/ref/ref-single.html#crefsqlj95081
>
> Hope this helps,
> -Rick
>
>
--
View this message in context: http://www.nabble.com/Recursive-query-on-common-table-tp25819772p25906990.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Recursive query on common table
Posted by Rick Hillegas <Ri...@Sun.COM>.
Vrin26 wrote:
> Hi,
>
> I need to do a recursive query on a single derby database table.
> In Derby database documents it is mentioned that this derby doesn't support
> recursive queries.
> Is there any other work around to handle this scenario?
>
> Thanks
>
>
You can push the recursion into a table function or into a database
procedure which returns a result set. The table function documentation
talks about wrapping external data sources, but you can also use table
functions to wrap complicated processing inside Derby itself. See
http://db.apache.org/derby/docs/10.5/devguide/devguide-single.html#cdevspecialtabfuncs
The CREATE PROCEDURE statement is documented here:
http://db.apache.org/derby/docs/10.5/ref/ref-single.html#crefsqlj95081
Hope this helps,
-Rick
Re: Recursive query on common table
Posted by Alain_Rastoul <al...@free.fr>.
Hi Vrin26
You can represent your "tree" as an interval with each node of the tree
within the interval of his parent node .
Lets say each node has a name, a parent id, plus a left id and a right id
(left and right bounds of the interval).
Each child node must have a left and right id values between the left and
right id values of his parent node,
doing this way you can select the whole tree of all childs of a node with a
single select.
The trick is to maintain the left and right id values consistent with
triggers each time you insert, updatge , delete data in your table,
recompute the
left and right boudns using the parent id .
something like :
select node.id, node.name
from node
where node.leftId >= parent.leftId
and node.rightId <= parent.rightId
for a given parent node
(you can join it in the select)
Hope it helps
regards
Alain
"Vrin26" <di...@in.ibm.com> a �crit
dans le message de news: 25885395.post@talk.nabble.com...
Sylvain,
What you had posted is exactly my problem.
I had already used same query which Geoffrey had mentioned but it doesn't
solve my problem
as I have to find all descendants.
Thanks for the information, i will go through all the solutions suggested in
this chain.
I will post my solution as and when it gets fixed.
Thanks.
Sylvain Leroux wrote:
>
> Geoff hendrey a �crit :
>> SELECT parent.id, child.id FROM T as parent, T as Child WHERE
>> child.parent=parent.id ORDER BY parent.id
>>
>> In the case where the tree is a doubly linked list, you'd get this
>>
>> parent.id | child.id
>> 1 2
>> 2 3
>> 3 4
>> 4 5
> With such a query, you could only find a direct descendant of a node. I
> think
> here the problem is to find all the descendants (or ascendants) from a
> node.
> Regardless the number of intermediate levels.
>
> If you use adjacency lists, recursion is the answer. But is not directly
> supported by Derby. As Rick Hillegas suggested, one solution would be to
> encapsulate the recursive part or your query in a custom table function
> (written
> in Java). I've never done that, so if you do, I would find of great
> benefice if
> you post your solution on the mailing list (or the wiki)!
>
>
> Otherwise, there is an article on mysql.com that describe that exact kind
> of
> problem and propose a solution using nested sets instead. That way, you no
> longer needs recursion:
> http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
>
>
> Hope this helps,
> Sylvain
>
> --
> Website: http://www.chicoree.fr
>
>
>
>
--
View this message in context:
http://www.nabble.com/Recursive-query-on-common-table-tp25819772p25885395.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Recursive query on common table
Posted by Vrin26 <di...@in.ibm.com>.
Sylvain,
What you had posted is exactly my problem.
I had already used same query which Geoffrey had mentioned but it doesn't
solve my problem
as I have to find all descendants.
Thanks for the information, i will go through all the solutions suggested in
this chain.
I will post my solution as and when it gets fixed.
Thanks.
Sylvain Leroux wrote:
>
> Geoff hendrey a écrit :
>> SELECT parent.id, child.id FROM T as parent, T as Child WHERE
>> child.parent=parent.id ORDER BY parent.id
>>
>> In the case where the tree is a doubly linked list, you'd get this
>>
>> parent.id | child.id
>> 1 2
>> 2 3
>> 3 4
>> 4 5
> With such a query, you could only find a direct descendant of a node. I
> think
> here the problem is to find all the descendants (or ascendants) from a
> node.
> Regardless the number of intermediate levels.
>
> If you use adjacency lists, recursion is the answer. But is not directly
> supported by Derby. As Rick Hillegas suggested, one solution would be to
> encapsulate the recursive part or your query in a custom table function
> (written
> in Java). I've never done that, so if you do, I would find of great
> benefice if
> you post your solution on the mailing list (or the wiki)!
>
>
> Otherwise, there is an article on mysql.com that describe that exact kind
> of
> problem and propose a solution using nested sets instead. That way, you no
> longer needs recursion:
> http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
>
>
> Hope this helps,
> Sylvain
>
> --
> Website: http://www.chicoree.fr
>
>
>
>
--
View this message in context: http://www.nabble.com/Recursive-query-on-common-table-tp25819772p25885395.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
RE: Corrupt derby db
Posted by Brian Spindler <bs...@netuitive.com>.
I appreciate the time Rick but I apologize. I was preparing myself for
a discussion with the customer but now it seems they have simply used
the term "corruption" to indicate that stale records were in the
database. False alarm! We're using 10.5.3 release btw (latest and
greatest! )
-----Original Message-----
From: Richard.Hillegas@Sun.COM [mailto:Richard.Hillegas@Sun.COM]
Sent: Friday, October 16, 2009 11:16 AM
To: Derby Discussion
Subject: Re: Corrupt derby db
Hi Brian,
It's hard to say anything useful without more information. For starters:
o What version of Derby is being used?
o What's the stack trace which signals that you have a data corruption?
Thanks,
-Rick
Brian Spindler wrote:
> I don't have specifics at the moment (I can get them) but we have a
> small application that uses the Embedded version of Derby. Problem is
> every 3-6mos after running at customer site they get a corrupted derby
> database. I was curious if this was a programming error and what
> typically causes corruption to derby? It seems to happen pretty
> consistently although I have no idea why.
>
> Thanks for any insights.
>
> -Brian
>
>
Re: Corrupt derby db
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Brian,
It's hard to say anything useful without more information. For starters:
o What version of Derby is being used?
o What's the stack trace which signals that you have a data corruption?
Thanks,
-Rick
Brian Spindler wrote:
> I don't have specifics at the moment (I can get them) but we have a
> small application that uses the Embedded version of Derby. Problem is
> every 3-6mos after running at customer site they get a corrupted derby
> database. I was curious if this was a programming error and what
> typically causes corruption to derby? It seems to happen pretty
> consistently although I have no idea why.
>
> Thanks for any insights.
>
> -Brian
>
>
Corrupt derby db
Posted by Brian Spindler <bs...@netuitive.com>.
I don't have specifics at the moment (I can get them) but we have a
small application that uses the Embedded version of Derby. Problem is
every 3-6mos after running at customer site they get a corrupted derby
database. I was curious if this was a programming error and what
typically causes corruption to derby? It seems to happen pretty
consistently although I have no idea why.
Thanks for any insights.
-Brian
RE: auto generated ids
Posted by Brian Spindler <bs...@netuitive.com>.
Thanks Knut!
I removed the extra INSERT statements and simply appended the values as
you shown in your example and it worked great. Thanks for clarifying.
-----Original Message-----
From: Knut.Hatlen@Sun.COM [mailto:Knut.Hatlen@Sun.COM]
Sent: Tuesday, October 13, 2009 10:06 AM
To: Derby Discussion
Subject: Re: auto generated ids
Brian Spindler <bs...@netuitive.com> writes:
> I have a table let's say FOO with a column name 'ID' and it is auto
> increment.
>
> Now I want to insert one row into that table and then insert 10 rows
> into an adjacency table with the new generated id. I thought
something
> like this might work:
>
> INSERT INTO FOO(NAME) VALUES ('BAR');
> INSERT INTO ADJTBL (FOO_ID, BAR_NAME)
> VALUES (
> (SELECT DISTINCT IDENTITY_VAL_LOCAL() FROM FOO),
> 'MyName');
> ...
>
> and while the first INSERT works the IDENTITY_VAL_LOCAL() then reads
the
> generated id from ADJTBL as opposed to the FOO table. What am I doing
> wrong? Is this possible? I am running this from a .sql file within
IJ
> tool.
IDENTITY_VAL_LOCAL() returns the most recently assigned value of an
identity column for a connection in a single-row INSERT statement, so
once you've inserted a new row into ADJTBL, the return value from
IDENTITY_VAL_LOCAL() will change. See
http://db.apache.org/derby/docs/10.5/ref/rrefidentityvallocal.html.
Inserting all 10 rows into ADJTBL in one INSERT statement should work,
though:
INSERT INTO FOO(NAME) VALUES ('BAR');
INSERT INTO ADJTBL (FOO_ID, BAR_NAME) VALUES
(IDENTITY_VAL_LOCAL(), 'MyName1'),
(IDENTITY_VAL_LOCAL(), 'MyName2'),
...
(IDENTITY_VAL_LOCAL(), 'MyName10');
--
Knut Anders
Re: auto generated ids
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Brian Spindler <bs...@netuitive.com> writes:
> I have a table let's say FOO with a column name 'ID' and it is auto
> increment.
>
> Now I want to insert one row into that table and then insert 10 rows
> into an adjacency table with the new generated id. I thought something
> like this might work:
>
> INSERT INTO FOO(NAME) VALUES ('BAR');
> INSERT INTO ADJTBL (FOO_ID, BAR_NAME)
> VALUES (
> (SELECT DISTINCT IDENTITY_VAL_LOCAL() FROM FOO),
> 'MyName');
> ...
>
> and while the first INSERT works the IDENTITY_VAL_LOCAL() then reads the
> generated id from ADJTBL as opposed to the FOO table. What am I doing
> wrong? Is this possible? I am running this from a .sql file within IJ
> tool.
IDENTITY_VAL_LOCAL() returns the most recently assigned value of an
identity column for a connection in a single-row INSERT statement, so
once you've inserted a new row into ADJTBL, the return value from
IDENTITY_VAL_LOCAL() will change. See
http://db.apache.org/derby/docs/10.5/ref/rrefidentityvallocal.html.
Inserting all 10 rows into ADJTBL in one INSERT statement should work,
though:
INSERT INTO FOO(NAME) VALUES ('BAR');
INSERT INTO ADJTBL (FOO_ID, BAR_NAME) VALUES
(IDENTITY_VAL_LOCAL(), 'MyName1'),
(IDENTITY_VAL_LOCAL(), 'MyName2'),
...
(IDENTITY_VAL_LOCAL(), 'MyName10');
--
Knut Anders
auto generated ids
Posted by Brian Spindler <bs...@netuitive.com>.
I have a table let's say FOO with a column name 'ID' and it is auto
increment.
Now I want to insert one row into that table and then insert 10 rows
into an adjacency table with the new generated id. I thought something
like this might work:
INSERT INTO FOO(NAME) VALUES ('BAR');
INSERT INTO ADJTBL (FOO_ID, BAR_NAME)
VALUES (
(SELECT DISTINCT IDENTITY_VAL_LOCAL() FROM FOO),
'MyName');
...
and while the first INSERT works the IDENTITY_VAL_LOCAL() then reads the
generated id from ADJTBL as opposed to the FOO table. What am I doing
wrong? Is this possible? I am running this from a .sql file within IJ
tool.
Thanks,
Brian
Re: Recursive query on common table
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Sylvain Leroux <sl...@wanadoo.fr> writes:
> If you use adjacency lists, recursion is the answer. But is not
> directly supported by Derby. As Rick Hillegas suggested, one solution
> would be to encapsulate the recursive part or your query in a custom
> table function (written in Java). I've never done that, so if you do,
> I would find of great benefice if you post your solution on the
> mailing list (or the wiki)!
The SQL standard has recursive queries these days (WITH RECURSIVE,
cf. SQL 2003, vol 2, section 7.13 <query expression>), sadly not
supported by Derby. Do we we have a volunteer to implement it? ;-)
Dag
Re: Recursive query on common table
Posted by Rick Hillegas <Ri...@Sun.COM>.
Sylvain Leroux wrote:
> Geoff hendrey a écrit :
>> SELECT parent.id, child.id FROM T as parent, T as Child WHERE
>> child.parent=parent.id ORDER BY parent.id
>>
>> In the case where the tree is a doubly linked list, you'd get this
>>
>> parent.id | child.id
>> 1 2
>> 2 3
>> 3 4
>> 4 5
> With such a query, you could only find a direct descendant of a node.
> I think here the problem is to find all the descendants (or
> ascendants) from a node. Regardless the number of intermediate levels.
>
> If you use adjacency lists, recursion is the answer. But is not
> directly supported by Derby. As Rick Hillegas suggested, one solution
> would be to encapsulate the recursive part or your query in a custom
> table function (written in Java). I've never done that, so if you do,
> I would find of great benefice if you post your solution on the
> mailing list (or the wiki)!
If you do pursue this approach, you may save yourself some time by
reading the white paper on table functions posted here:
http://developers.sun.com/javadb/reference/whitepapers/index.jsp In
particular, you should be able to extend EnumeratorTableFunction, a
handy wrapper class which does most of the work required to turn an
arbitrary Enumeration, Iterator, or Iterable into a table function.
Hope this helps,
-Rick
>
>
> Otherwise, there is an article on mysql.com that describe that exact
> kind of problem and propose a solution using nested sets instead. That
> way, you no longer needs recursion:
> http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
>
>
> Hope this helps,
> Sylvain
>
Re: Recursive query on common table
Posted by Sylvain Leroux <sl...@wanadoo.fr>.
Geoff hendrey a écrit :
> SELECT parent.id, child.id FROM T as parent, T as Child WHERE
> child.parent=parent.id ORDER BY parent.id
>
> In the case where the tree is a doubly linked list, you'd get this
>
> parent.id | child.id
> 1 2
> 2 3
> 3 4
> 4 5
With such a query, you could only find a direct descendant of a node. I think
here the problem is to find all the descendants (or ascendants) from a node.
Regardless the number of intermediate levels.
If you use adjacency lists, recursion is the answer. But is not directly
supported by Derby. As Rick Hillegas suggested, one solution would be to
encapsulate the recursive part or your query in a custom table function (written
in Java). I've never done that, so if you do, I would find of great benefice if
you post your solution on the mailing list (or the wiki)!
Otherwise, there is an article on mysql.com that describe that exact kind of
problem and propose a solution using nested sets instead. That way, you no
longer needs recursion:
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
Hope this helps,
Sylvain
--
Website: http://www.chicoree.fr
Re: Recursive query on common table
Posted by Geoff hendrey <ge...@yahoo.com>.
Interesting. I figured you were trying a linked list or a tree. Would this work?
SELECT parent.id, child.id FROM T as parent, T as Child WHERE child.parent=parent.id ORDER BY parent.id
In the case where the tree is a doubly linked list, you'd get this
parent.id | child.id
1 2
2 3
3 4
4 5
In my experience, table aliasing using the 'AS' keyword is the way to go.
-geoff
________________________________
From: Vrin26 <di...@in.ibm.com>
To: derby-user@db.apache.org
Sent: Sun, October 11, 2009 10:25:41 PM
Subject: Re: Recursive query on common table
Hi Geoffrey,
I am trying to create a hierarchy tree structure.
In my table I have columns Parent and Child.
Parent column can also have child ids and it's subchild ids in Child column.
I need to loop over table until i get chain of all parent, child, subchilds
and so on.
I don't have information about number of childs a parent can have, so I have
to loop until i don't get any more childs for parent.
Is there a way to achive this in Derby database?
Thanks
Geoffrey Hendrey wrote:
>
> Could you give some more detail on what the query will be used for?
>
>
> On Oct 9, 2009, at 4:52 AM, Vrin26 <di...@in.ibm.com> wrote:
>
>
> Hi,
>
> I need to do a recursive query on a single derby database table.
> In Derby database documents it is mentioned that this derby doesn't
> support
> recursive queries.
> Is there any other work around to handle this scenario?
>
> Thanks
>
> --
> View this message in context:
> http://www.nabble.com/Recursive-query-on-common-table-tp25819772p25819772.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
>
>
>
--
View this message in context: http://www.nabble.com/Recursive-query-on-common-table-tp25819772p25850680.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Recursive query on common table
Posted by Vrin26 <di...@in.ibm.com>.
Hi Geoffrey,
I am trying to create a hierarchy tree structure.
In my table I have columns Parent and Child.
Parent column can also have child ids and it's subchild ids in Child column.
I need to loop over table until i get chain of all parent, child, subchilds
and so on.
I don't have information about number of childs a parent can have, so I have
to loop until i don't get any more childs for parent.
Is there a way to achive this in Derby database?
Thanks
Geoffrey Hendrey wrote:
>
> Could you give some more detail on what the query will be used for?
>
>
> On Oct 9, 2009, at 4:52 AM, Vrin26 <di...@in.ibm.com> wrote:
>
>
> Hi,
>
> I need to do a recursive query on a single derby database table.
> In Derby database documents it is mentioned that this derby doesn't
> support
> recursive queries.
> Is there any other work around to handle this scenario?
>
> Thanks
>
> --
> View this message in context:
> http://www.nabble.com/Recursive-query-on-common-table-tp25819772p25819772.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
>
>
>
--
View this message in context: http://www.nabble.com/Recursive-query-on-common-table-tp25819772p25850680.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.