Login or Register to Ask a Question and Join Our Community


Programming

Hierarchical Query for Sybase database

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming Hierarchical Query for Sybase database
# 1  
Old 06-21-2016
Hierarchical Query for Sybase database
Team

I am using DBartisan tool for sybase database.
I have a table that has below fields Employee_ID,EMP_Name,First_Nm,Last_Nm,Emp_Designation,Employee's_Manager
is it possible to retrieve hierarchical data based on below fields
Emp_Designation will have Soft Engg,SSE,Team Lead, Architect,Vice President,President,CEO etc
Employee's_Manager names are again referred under EMP_Name where again he would be reporting to another manager under
"Employee's_Manager" column

Will it be possible to retrieve hierarchical data based on above fields/values by comparing to Employee's_Manager designation
Any help appreciated.
Last edited by Perlbaby; 06-21-2016 at 12:03 PM..
# 2  
Old 06-21-2016
What kind of Sybase database are you using - ASE, ADS, IQ, SQL Anywhere etc.?
And what's the version of your Sybase database?
# 3  
Old 06-22-2016
Thanks for replying
Please find the details .

Sybase is ASE(Adaptive Server Enterprise/15.7/EBF )
For query analysis using Embarcadero DBArtisan 9.1.2 version

Thanks in advance
# 4  
Old 06-22-2016
Thanks. You have a modern version of Sybase.
Try the following query.

Code:
--
with emp (emp_id, emp_name, emp_desg, emp_mgr) as (
    select 10, 'Adam', 'CEO',        null union all
    select 20, 'Brian', 'President', 10   union all
    select 30, 'Chuck', 'VP',        10   union all
    select 40, 'Dan',  'Architect',  20   union all
    select 50, 'Eric', 'SSE',        20   union all
    select 60, 'Fred', 'Team Lead',  30   union all
    select 70, 'Greg', 'SE',         60
),
hier_tree (emp_id, emp_name, emp_desg, emp_mgr, level, path) as (
    -- Seed query
    select emp_id, emp_name, emp_desg, emp_mgr, 1 as level, cast('/' + emp_name as varchar) as path
      from emp
     where emp_mgr is null
    union all
    -- Recursive query
    select e.emp_id, e.emp_name, e.emp_desg, e.emp_mgr, h.level + 1, cast(h.path + '/' + e.emp_name as varchar)
      from hier_tree h, emp e
     where h.emp_id = e.emp_mgr
)
select *
  from hier_tree

I do not have Sybase db to test it; I tested it in SQL Server 2008.
Since Sybase and SQL Server share a common code base, it should work in Sybase, maybe with a few minor tweaks.

Here's my execution:

Code:
3>
4> select @@version
5> go

------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
        Aug 19 2014 12:21:34
        Copyright (c) Microsoft Corporation
        Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


(1 rows affected)
1>
2> --
3> with emp (emp_id, emp_name, emp_desg, emp_mgr) as (
4>     select 10, 'Adam', 'CEO',        null union all
5>     select 20, 'Brian', 'President', 10   union all
6>     select 30, 'Chuck', 'VP',        10   union all
7>     select 40, 'Dan',  'Architect',  20   union all
8>     select 50, 'Eric', 'SSE',        20   union all
9>     select 60, 'Fred', 'Team Lead',  30   union all
10>    select 70, 'Greg', 'SE',         60
11> ),
12> hier_tree (emp_id, emp_name, emp_desg, emp_mgr, level, path) as (
13>     -- Seed query
14>     select emp_id, emp_name, emp_desg, emp_mgr, 1 as level, cast('/' + emp_name as varchar) as path
15>       from emp
16>      where emp_mgr is null
17>     union all
18>     -- Recursive query
19>     select e.emp_id, e.emp_name, e.emp_desg, e.emp_mgr, h.level + 1, cast(h.path + '/' + e.emp_name as varchar)
20>       from hier_tree h, emp e
21>      where h.emp_id = e.emp_mgr
22> )
23> select *
24>   from hier_tree
25> go
emp_id      emp_name emp_desg  emp_mgr     level       path
----------- -------- --------- ----------- ----------- ------------------------------
         10 Adam     CEO              NULL           1 /Adam
         20 Brian    President          10           2 /Adam/Brian
         30 Chuck    VP                 10           2 /Adam/Chuck
         60 Fred     Team Lead          30           3 /Adam/Chuck/Fred
         70 Greg     SE                 60           4 /Adam/Chuck/Fred/Greg
         40 Dan      Architect          20           3 /Adam/Brian/Dan
         50 Eric     SSE                20           3 /Adam/Brian/Eric

(7 rows affected)
1>
2>
3>

A few notes follow:

(1) Sybase might require you to use the keyword "recursive" after the keyword "with". So "with emp..." becomes "with recursive emp...". It is optional as per ANSI SQL standard. Some databases like PostgreSQL require it; others like Oracle and DB2 don't.

(2) The "with emp" subquery was created by me to generate the data on-the-fly, without having to create a table. You already have the emp data in a table, so you may want to use that table. If you do that, then your query begins from "hier_tree". So: "with hier_tree..." or "with recursive hier_tree..." - whichever works.

(3) Concatenation operator "+" should work in Sybase.

(4) Not sure about the "cast()" function in Sybase. SQL Server throws an error message if I don't use it.
Last edited by durden_tyler; 06-22-2016 at 03:48 PM..
# 5  
Old 06-23-2016
Thanks for the reply .

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 "
# 6  
Old 06-24-2016
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.
This User Gave Thanks to durden_tyler For This Post:
# 7  
Old 06-27-2016
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:
Perlbaby
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. 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... (0 Replies)
Discussion started by: Perlbaby
0 Replies

2. 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... (2 Replies)
Discussion started by: Perlbaby
2 Replies

3. 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 Replies)
Discussion started by: amit.mathur08
8 Replies

4. 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... (3 Replies)
Discussion started by: danin
3 Replies

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... (2 Replies)
Discussion started by: chatieremerrill
2 Replies

6. 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... (0 Replies)
Discussion started by: jisha
0 Replies

7. 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... (2 Replies)
Discussion started by: Alaeddin
2 Replies

8. 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... (10 Replies)
Discussion started by: Alaeddin
10 Replies

9. 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 (7 Replies)
Discussion started by: bhgopi
7 Replies
Login or Register to Ask a Question