Hierarchical Query for Sybase database Post: 302976305

Sponsored Content

Top Forums Programming Hierarchical Query for Sybase database Post 302976305 by durden_tyler on Monday 27th of June 2016 06:18:16 PM

06-27-2016

Registered User

Over the weekend, I was able to install a very basic free, developer's edition of SAP Sybase ASE 16.0 on my Debian 8 OS.
The SQL implementation is quite primitive compared to those by the big boys. No "with" subqueries (Common Table Expressions), no recursive hierarchical queries, no group by extensions like rollup or cube, no object relational SQL and no support for analytic / window functions!

Here's my testcase for implementation of the hierarchical data querying mentioned in my earlier post:

Code:

declare @depth int
declare @rowcount int
set @depth = 1
while (1 = 1)
begin
    insert into #hier_tree (id, name, desg, mgr, depth, root, path)
    select e.id, e.name, e.desg, e.mgr,
           h.depth + 1 as depth,
           h.root,
           h.path + '/' + e.name as path
      from #hier_tree h, emp e
     where h.id = e.mgr
       and h.depth = @depth
    set @rowcount = @@ROWCOUNT
    if (@rowcount = 0)
        break
    set @depth = @depth + 1
end
go

The complete testcase and its execution follows:

Code:

1>
2> select @@version as ver
3> go

  ver
--------------------------------------------------------------------------------------------------------------------------------------------
  Adaptive Server Enterprise/16.0 GA PL01/EBF 22544 SMP/P/x86_64/Enterprise Linux/ase160sp00pl01/3523/64-bit/FBO/Tue Apr 15 13:24:31 2014

(1 row affected)
1>
2>
3> --
4> create table emp (
5>     id    int         not null,
6>     name  varchar(20) not null,
7>     desg  varchar(20) not null,
8>     mgr   int         null
9> )
10> go
1>
2> insert into emp (id, name, desg, mgr)
3> select  10, 'Adam',    'CEO',       null union all
4> select  20, 'Brian',   'President', 10   union all
5> select  30, 'Chuck',   'VP',        10   union all
6> select  40, 'Dan',     'Architect', 20   union all
7> select  50, 'Eric',    'SSE',       20   union all
8> select  60, 'Fred',    'Team Lead', 30   union all
9> select  70, 'Greg',    'SE',        60   union all
10> select 100, 'Patrick', 'Executive', null union all
11> select 200, 'Quinn',   'President', 100  union all
12> select 300, 'Roger',   'VP',        100
13> go
(10 rows affected)
1>
2> -- Two hierarchies exist; one starting from Adam and the other from Patrick
3> select * from emp
4> go
 id          name                 desg                 mgr
 ----------- -------------------- -------------------- -----------
          10 Adam                 CEO                         NULL
          20 Brian                President                     10
          30 Chuck                VP                            10
          40 Dan                  Architect                     20
          50 Eric                 SSE                           20
          60 Fred                 Team Lead                     30
          70 Greg                 SE                            60
         100 Patrick              Executive                   NULL
         200 Quinn                President                    100
         300 Roger                VP                           100

(10 rows affected)
1>
2> -- Create the temp table
3> --
4> create table #hier_tree (
5>     id       int             not null,
6>     name     varchar(20)     not null,
7>     desg     varchar(20)     not null,
8>     mgr      int             null,
9>     depth    int             not null,
10>     root     varchar(20)     not null,
11>     path     varchar(60)    not null
12> )
13> go
1>
2> -- Seed query
3> insert into #hier_tree (id, name, desg, mgr, depth, root, path)
4> select e.id, e.name, e.desg, e.mgr,
5>        1 as depth,
6>        e.name as root,
7>        '/' + e.name as path
8>   from emp e
9>  where e.mgr is null
10> go
(2 rows affected)
1>
2> --
3> -- The top level root nodes (Adam and Patrick) should be loaded now
4> --
5> select * from #hier_tree
6> go
 id          name                 desg                 mgr         depth       root                 path
 ----------- -------------------- -------------------- ----------- ----------- -------------------- ------------------------------------------------------------
          10 Adam                 CEO                         NULL           1 Adam                 /Adam
         100 Patrick              Executive                   NULL           1 Patrick              /Patrick

(2 rows affected)
1>
2> --
3> -- Now load the entire hierarchy tree using a while loop, recursively joining the temp table with emp
4> --
5> declare @depth int
6> declare @rowcount int
7> set @depth = 1
8> while (1 = 1)
9> begin
10>     insert into #hier_tree (id, name, desg, mgr, depth, root, path)
11>     select e.id, e.name, e.desg, e.mgr,
12>            h.depth + 1 as depth,
13>            h.root,
14>            h.path + '/' + e.name as path
15>       from #hier_tree h, emp e
16>      where h.id = e.mgr
17>        and h.depth = @depth
18>     set @rowcount = @@ROWCOUNT
19>     if (@rowcount = 0)
20>         break
21>     set @depth = @depth + 1
22> end
23> go
(1 row affected)
(4 rows affected)
(1 row affected)
(1 row affected)
(3 rows affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(0 rows affected)
(1 row affected)
1>
2> --
3> -- Check the results
4> --
5> select * from #hier_tree
6> go
 id          name                 desg                 mgr         depth       root                 path
 ----------- -------------------- -------------------- ----------- ----------- -------------------- ------------------------------------------------------------
          10 Adam                 CEO                         NULL           1 Adam                 /Adam
         100 Patrick              Executive                   NULL           1 Patrick              /Patrick
          20 Brian                President                     10           2 Adam                 /Adam/Brian
          30 Chuck                VP                            10           2 Adam                 /Adam/Chuck
         200 Quinn                President                    100           2 Patrick              /Patrick/Quinn
         300 Roger                VP                           100           2 Patrick              /Patrick/Roger
          40 Dan                  Architect                     20           3 Adam                 /Adam/Brian/Dan
          50 Eric                 SSE                           20           3 Adam                 /Adam/Brian/Eric
          60 Fred                 Team Lead                     30           3 Adam                 /Adam/Chuck/Fred
          70 Greg                 SE                            60           4 Adam                 /Adam/Chuck/Fred/Greg

(10 rows affected)
1>
2> --
3> -- Sort the data so each hierarchy tree is seen clearly
4> --
5> select * from #hier_tree order by root, depth
6> go
 id          name                 desg                 mgr         depth       root                 path
 ----------- -------------------- -------------------- ----------- ----------- -------------------- ------------------------------------------------------------
          10 Adam                 CEO                         NULL           1 Adam                 /Adam
          20 Brian                President                     10           2 Adam                 /Adam/Brian
          30 Chuck                VP                            10           2 Adam                 /Adam/Chuck
          40 Dan                  Architect                     20           3 Adam                 /Adam/Brian/Dan
          50 Eric                 SSE                           20           3 Adam                 /Adam/Brian/Eric
          60 Fred                 Team Lead                     30           3 Adam                 /Adam/Chuck/Fred
          70 Greg                 SE                            60           4 Adam                 /Adam/Chuck/Fred/Greg
         100 Patrick              Executive                   NULL           1 Patrick              /Patrick
         300 Roger                VP                           100           2 Patrick              /Patrick/Roger
         200 Quinn                President                    100           2 Patrick              /Patrick/Quinn

(10 rows affected)
1>
2> --
3> -- Testcase over. Time to clean up...
4> --
5> drop table #hier_tree
6> go
1>
2> --
3> drop table emp
4> go
1>
2>

Hope that helps.

This User Gave Thanks to durden_tyler For This Post:

durden_tyler

View Public Profile for durden_tyler

Find all posts by durden_tyler

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Connect to sybase database using Korn shell script

Hi, Can anyone please give me a script or let me know how to connect to a sybase database and execute a query using Korn shell scripts.Am new to Unix but i need to do this ASAP. Please help. Thanks, Gops

2. Shell Programming and Scripting

Dynamic update loop query on Sybase database

Hello Guys, I'm new to Shell scripting, and i need someone to help me with this issue: I'm trying to do a dynamic update query on Sysbase database table using shell script. Lets say, the Update query is "update Table set id=X" , where X is dynamic value for the loop index. If the loop is...

3. Shell Programming and Scripting

Print out loop index on the console after executing each sybase DB query

Hello Guys, Well, using shell script, I'm doing loop on DB query as below: isql -Usa -Ptest -I /opt/sybase/interfaces << EOF use testdb go declare @i int select @i = 1 while(@i <= 5) begin Insert into TEST values (@i,"Test","TestDesc") select @i = @i + 1 end go EOF The Issue...

4. Shell Programming and Scripting

Database Query

Hi, Am using informix database. When i give "isqlrf <database name> - " from the command prompt , i get connected to the database and i can run sql queries.. I am moving to freeBSD and when i give the same command at the prompt i get an error : Index already exists on column Why is this...

5. Programming

Need help with complex SQL query (Sybase)

Hello, I have three tables. I need an SQL query (preferably Sybase) that will return all of the stringID values of table B where the following conditions exist: (1) B.intID = A.intID (2) B.intID != C.intID or (B.intID = C.intID and (C.v1 = 0 or C.v2...

6. Windows & DOS: Issues & Discussions

how to connect to sybase database?

hi, I'd like to connect to a Sybase ASE 12 through a a graphic user interface (GUI) that run on windows and solaris10, because i need to do some querys. The database is running on solaris 10. I'm not an expert using databases, but i know how to use some SQL commands through command line...

7. Shell Programming and Scripting

Execute stored procedure through script in sybase database and store the output in a .csv file

Hi, I have a sybase stored procedure which takes two input parameters (start_date and end_date) and when it get executed, it gives few records as an output. I want to write a unix script (ksh) which login to the sybase database, then execute this stored procedure (takes the input parameter as...

8. Programming

Sybase ASE: Query to find correct format issue.

Hi Team , I am new to Sybase Adaptive Server Enterprise/15.7 (ASE) and need some guidance to find the different values in serial format column. SELECT DISTINCT SERIAL_FORMAT FROM PRODUCTS It has values with below format which contains 12 digits hexadecimal characters with...

9. Programming

Sybase ASE - Query Tuning - Need Suggestion

Dear Team Please provide suggestion on below query which is used in Sybase Adaptive Server Enterprise/15.7 (ASE). Query takes more time > 30 Mins to 1 Hr All required indexes are built Can we have any efficient approach to get the data retrieval faster for below query.Any help...