derived table and recursive table structure

[Former Member]

Hello and happy new year,

I like to create a filter based on a table which has a

recursive structure.

Therefore I used the way IBM explains it (http://www.ibm.com/developerworks/db2/library/techarticle/dm-0510rielau/).

WITH n(empid, name) AS

(SELECT empid, name

FROM emp

WHERE name = @Propmt(...)

UNION ALL

SELECT nplus1.empid, nplus1.name

FROM emp as nplus1, n

WHERE n.empid = nplus1.mgrid)

SELECT name FROM n;

Now I like to create a derived table (with the above statement) in the universe and connect it to an other table so that I can use it as a filter.

But unfortunately I get an Error when I click the button "check syntax"

Exception: DBD, [IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "AS" was found following "WITH n(empid, name)".

Expected tokens may include: "JOIN". SQLSTATE=42601 State: 42601

If I run the statement in an "SQL Commander" directly against the database it works.

Do you have any ideas?

thanks in advance

[Former Member]

Hi

I don't think the With parameters are standard SQL. Designer may not support it.

Have you tried

SELECT 
name 
from 
 (SELECT empid, name 
FROM emp
WHERE name = @Prompt(...)
UNION ALL
SELECT nplus1.empid, nplus1.name 
FROM emp as nplus1, n
WHERE n.empid = nplus1.mgrid)

An alternate might be to create a view on your DB which wraps this code and then use that

Regards

Alan

[Former Member]

Hello Alan,

thanks a lot for your fast response.

I guess you are right WITH is no standard SQL.

But it is the only way (I know) to query a recursive table in DB2.

In this case (the IBM Example) the table looks like:

 CREATE TABLE emp(empid  INTEGER NOT NULL PRIMARY KEY,
                    name   VARCHAR(10),
                    salary DECIMAL(9, 2),
                    mgrid  INTEGER);

 INSERT INTO emp
       VALUES ( 1, 'Jones',    30000, 10),
               ( 2, 'Hall',     35000, 10),
               ( 3, 'Kim',      40000, 10),
               ( 4, 'Lindsay',  38000, 10),
               ( 5, 'McKeough', 42000, 11),
               ( 6, 'Barnes',   41000, 11),
               ( 7, 'O''Neil',  36000, 12),
               ( 8, 'Smith',    34000, 12),
               ( 9, 'Shoeman',  33000, 12),
               (10, 'Monroe',   50000, 15),
               (11, 'Zander',   52000, 16),
               (12, 'Henry',    51000, 16),
               (13, 'Aaron',    54000, 15),
               (14, 'Scott',    53000, 16),
               (15, 'Mills',    70000, 17),
               (16, 'Goyal',    80000, 17),
               (17, 'Urbassek', 95000, NULL);

And I like to know "Who works directly or indirectly for 'Goyal'?"

The result should be

Goyal
Zander
Henry
Scott
McKeough
Barnes
O'Neil
Smith
Shoeman

The SQL-Statement you mentioned will not bring that result.

Yes, an alternate might be to create a view on my DB but

I am working in an very restrictive environment. So I fear

I will not get that view on the DB because the DB Admin

will (I guess) not accept an additional VIEW or it would

take a really long time to apply.

Do you have any other ideas?

thanks in advance

[Former Member]

Hi Bodo,

Sorry, I missed recursive part. Is the hierarchy limited - ie if the max dept is fixed at 4 you can do something like

select
 level1.name,
 level2.name,
 level3.name,
 level4.name
from
 emp level1
left outer join emp level2
on level1.empid = level2.mgrid
left outer join emp level3
on level2.empid = level3.mgrid
left outer join emp level4
on level3.empid = level4.mgrid
where
level1.mgrid is null

I would have to try this to ensure it worked and you would have to know the level of the person you are looking for.

Hope this helps

Alan

[PPaolo]

Hello,

I can confirm: a WITH will not work in a Derived Table.

The reason is that when the table is used, its definition gets wrapped in a FROM statement, this makes up a wrong syntax on the database.

E.g. if I define a derived table as:

Select name from emp where id=2

and then I create an object on top of it on the Name value, when I use the object the SQL sent to the database will be:

Select myderived.name from (select name from emp where id=2) myderived

This would work because you can nest a Select within a FROM but if you had a WITH than the syntax wouldn't work:

Select myderived.name from (WITH.... ) will cause an error if you run it on the database server.

For your specific problem, is the real table you have to report onto using of the same format of the example you are giving (e.g. emp_id, name, mgr_id) ? There are alternative ways to describe an organization in a table given the fact that SQL is not so good at parsing parent-child relationships.

There is a specific format for organizational hierarchies which works better (name, emp_id, left_id, right_id) if your table is of this format, or if you can change the table to this format I can give you more information on how to retrieve a hierarchy out of that.

Best regards

[Former Member]

Hello and thanks again for your responses,

@Alan

unfortunately the hierarchy is not limited. There was already a thread

regarding a recursive table with has although a depth limit.

Therefore I was really happy that I found a solution to query a recursive table without

depth limit.

@Pierpaolo

I was afraid that this is the case. So I have bad luck. If I would use ORACLE it would work because I could use

SELECT name 
  FROM emp
  START WITH name = 'Goyal'
  CONNECT BY PRIOR empid = mgrid

and I guess this is "nestable" in a FROM Statement

unfortunately the database is already designed and productive so I can not change it.

I although think that it is not necessarily the best way to define a parent-child relationship

that way but I have to work with that structure now to build a few reports.

@all

Maybe a Solution (as Alan mentioned) is to create something in the direction stored procedure, no view because I have to provide the start name as a parameter ("WHERE name = @Prompt(...)"), and use it in the derived table.

Do you thing that is a good idea?

But with this Solution I have to add a "workaround store procedure" to the productive database for reporting purposes. I guess this will be hard to argue with the DB Admin.

Some other ideas?

Thanks in advance

[PPaolo]

Hello,

if you create a stored procedure to retrieve the data you cannot then join it back to the other tables of the database in a Universe (in a Stored Procedures Universe you can create business objects only out of the Stored Procedures output, not from Tables; and you cannot join Stored Procedures to anything else).

If you just want to have information about who reports to whom than the Stored Procedure approach will work, if you want to get additional information (e.g. the salary) by using other business objects, then it will not work.

You might create a Stored Proc in which builds a view of the hierarchy in a format which can be read by plain SQL (i.e. the emp_ID, left_ID,right_ID format as suggeted before). Then it will be possible to see the view as any other table into the Universe.

Best regards

PPaolo

Edited by: Pierpaolo Vezzosi on Jan 16, 2009 1:57 PM

[Former Member]

hi PPaolo,

Is it possible to use the syntax

select * from table('exec sp_name')

in derived tables

Regards

Alan