[
https://issues.apache.org/jira/browse/DERBY-11?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13285792#comment-13285792
]
Kathey Marsden commented on DERBY-11:
-------------------------------------
I think the first step would be to research the SQL Standard to determine if
there is a SQL standard syntax for this. Rick indicated that DB2 and Oracle
have different approaches. Hopefully there is a standard approach so that we
can implement it in Derby.
> Recursive SQL and WITH A(col list) as (Select col list From Table List)
> Support.
> --------------------------------------------------------------------------------
>
> Key: DERBY-11
> URL: https://issues.apache.org/jira/browse/DERBY-11
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Environment: ANY.
> Reporter: Ali Demir
>
> Right now, in Derby, there is no way to define a temporary Result Set to use
> in subsequent statements. This makes complicated concepts to be expressed in
> SQL either very very complicated and lengthy or simply impossible.
> DB2 has a simple and useful syntax using a "WITH" statement. It would be nice
> if Derby can support this. An example is as below:
> WITH A(COL1, COL2) as (SELECT COL1, COL2 FROM T1 WHERE condition)
> SELECT T2.COL3 FROM T2, A WHERE condition2
> It can be extended to include more WITH clauses:
> WITH A(COL1, COL2) as (SELECT COL1, COL2 FROM T1 WHERE condition)
> WITH B(COL3) as (SELECT COL3 FROM T1,A WHERE condition2)
> SELECT T2.COL5, B.COL3 FROM T2, A, B WHERE condition3
> and so on.
> Note that as the following example shows, the use of table correlation name
> in another subselect is NOT supported and cannot be a workaround:
> SELECT cols FROM (SELECT cols FROM T1) as A, (SELECT cols FROM T2,A where A
> relates to T2) as B where condition
> Another interesting aspect of these WITH clauses is their ability to make
> RECURSIVE SQL possible. In below example, definition of A includes a select
> from ITSELF:
> WITH A(COL1, COL2) as (SELECT COL1, COL2 FROM T1 UNION ALL SELECT COL1, COL2
> FROM T2, A where A.COL1=T2.COLN)
> SELECT COL1, COL2 FROM A WHERE condition2
> Recursion with a WITH clause relies on a specific syntax. Consult DB2
> documentation for more info about Recursion and WITH clause.
> Recursion is an important facility and it would be very very useful to have
> it in Derby.
> Recursion comes in very handy when a single table holds a hierarchy of rows
> that are related to each other with parent-child relationships of N-Levels
> where N is large or unknown in which case non-recursive solutions are either
> impossible or require complicated code at the Client side. With recursion
> possible at the SQL level, many problems can be reduced to single SQL
> statements instead of lengthy application code.
> Regards,
> Suavi Demir
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
