1

I have a problem I've previously solved with other DBMS but can't find a solution to run with Sybase-ASE 15.0. Is this possible with SQL (or Views) without custom StoredProcedures?

Input:

ID Name Parent
1  a
2  b    1
3  c    1
4  d    3

Output:

ID   PATH
1    a
2    a / b
3    a / c
4    a / c / d
Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
bw_üezi
  • 4,483
  • 4
  • 23
  • 41
  • found some additional information to the topic [here](http://www.codeproject.com/KB/database/HierarchySyBaseMSServer.aspx) (well didn't help me much so far...) – bw_üezi Feb 18 '11 at 20:12

2 Answers2

1

Edit: this is only supported by Sybase SQL Anywhere, not by the "professional" Adaptive Server Enterprise.

According to the manual, Sybase supports recursive common table expressions.

So the following should work:

WITH RECURSIVE hierarchy_path (id, node_path) AS 
(
  SELECT id, 
         name as node_path
  FROM the_unknown_table
  WHERE id = 1

  UNION ALL

  SELECT c.id, 
         p.node_path || ' / ' || c.name
  FROM the_unknown_table c
    JOIN hierarchy_path p ON p.id = c.parent_id 
)
SELECT *
FROM path
ORDER BY id

Not sure if Sybase uses the standard SQL concatenation operator || or something different. As Microsoft is ignoring the standard there, I guess Sybase uses the + as well.

  • I get `[Error Code: 156, SQL State: ZZZZZ] Incorrect syntax near the keyword 'WITH'.` Upon my research I found this should be possible with Sybase Anywhere but no hints to Sybase ASE. – bw_üezi Feb 18 '11 at 19:58
  • Ah, sorry. I just found this in the Sybase online manual. I didn't realize there was such a difference between ASA and ASE –  Feb 18 '11 at 20:02
0

Simple answer - No.

How to get list of values in GROUP_BY clause?

Community
  • 1
  • 1
kolchanov
  • 2,018
  • 2
  • 14
  • 32
  • looks ok if the recursion has only one level. but I don't see how this works with deeper level recursion. – bw_üezi Feb 18 '11 at 21:35