Quote:
Originally Posted by
Perlbaby
...
The WITH or WITH RECURSIVE does not work with Sybase ASE
Looks like this is only supported by Sybase SQL Anywhere, not by the "professional" Adaptive Server Enterprise .
It showing incorrect syntax near the keyword both "With and With recursive option "
Too bad Sybase does not support that syntax, since it is part of ANSI standard.
If, however, you have a procedural language in Sybase then you should be able to implement the pure SQL query into the procedural language.
Procedural languages in a database are the ones that allow you to have programming constructs like loops (for, while, do..until etc.) or branches (if...then). Examples of procedural languages are PL/SQL in Oracle, PL/pgSQL in PostgreSQL. You can write procedures/functions in procedural languages. Not sure if Sybase has procedural language built in it.
An explanation of the SQL query follows:
- The "seed query" fetches only the records that have "NULL" for the "emp_mgr". These are the records at the top of the hierarchies. In my sample data, there is only one such record (Adam). It's the root node and its level is set to 1.
- The "recursive query" then fetches the immediate children of the root node by joining it back to the "emp" table. So, for "Adam", we fetch records that have "Adam" as emp_mgr. That would be "Brian" and "Chuck". They are at level 1+1 = 2. Now our "hier_tree" has only two records - Brian and Chuck.
- The "recursive query" part of "hier_tree" is run again now. This time, it is joined with "emp" table again to find the immediate child nodes of (Brian, Chuck). That would be (Dan, Eric, Fred). Their level is 2+1 = 3. Now our "hier_tree" has three records (Dan, Eric, Fred).
- The "recursive query" part of "hier_tree" is run again now. This time, it is joined with "emp" table again to find the immediate child nodes of (Dan, Eric, Fred). So we get (Greg). His level is 3+1 = 4. Now our "hier_tree" has only one record (Greg).
- The "recursive query" part of "hier_tree" is run again now. It is joined with "emp" table to find the immediate child nodes of (Greg). This query returns no records, because there are no records in "emp" that has Greg as the manager. So, the execution of "hier_tree" is terminated. This is our exit condition - the "recursive query" part returning 0 records.
As you probably know, every recursive function must: (a) have an exit condition and (b) move nearer and nearer to the exit condition in each recursive call. That's exactly what is happening in the SQL query.
(I don't know how or why SQL Server shows the output as depth-first and not breadth-first traversal. In Oracle, I can specify the type of traversal.)
You could probably use temporary tables to store the intermediate results that are in "hier_tree" in the example. Hope that helps.