Hierarchical Query for Sybase database Post: 302976018

Sponsored Content

Top Forums Programming Hierarchical Query for Sybase database Post 302976018 by durden_tyler on Wednesday 22nd of June 2016 01:52:40 PM

06-22-2016

Registered User

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..

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...