|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/4/2006 3:39:00 PM
Posts: 3,
Visits: 1
|
|
Hello Gurus,
Is there an equivalent statement for CONNECT BY..PRIOR in Oracle. I'm trying to simulate a hierarchy in Teradata SQL and i'm using the WITH RECURSIVE query. It is not solving my requirement as it gives me
1,2
2,3
2,4
4,5
4,6
where the first column being the parent and the second- its child.
I need
1,2
1,3
1,4
1,5
1,6
2,3
2,4
2,5
2,6
4,5
4,6
Can anybody suggest a method to achieve this using ANSI/teradata SQL.
Thanks,
Hari
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 2 days ago @ 3:41:05 AM
Posts: 522,
Visits: 264
|
|
Hi Hari,
of course it's possible, you just have to code it the right way :-)
Maybe show us what you wrote so far...
Returning the hierarchy of Teradata databases & users as an example:
WITH RECURSIVE parent (databasename, path, level) AS
(SELECT databasename, databasename(varchar(3000)), 0 (byteint)
FROM dbc.databases d
WHERE databasename = ownername
UNION ALL
SELECT c.databasename, p.path || '.' || trim(c.databasename),level + 1
FROM dbc.databases C
,parent P
WHERE c.ownername = p.databasename
and c.databasename <> c.ownername
and level < 10
)
SELECT
databasename,
level,
substring(' ' from 1 for level * 2) || databasename,
path
FROM parent
order by path;
There's a good article from IBM covering porting Connect By to With:
http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0510rielau/
Dieter
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/4/2006 3:39:00 PM
Posts: 3,
Visits: 1
|
|
Hi Dieter,
Thanks for your Reply.
This is the recursive query i wrote
DATABASE EDW;
WITH RECURSIVE HARI_TMP
(mgr,emp,LEVEL) AS
(
SELECT mgr,emp,0
FROM EMPDATA
WHERE JOB_TITLE='PRESIDENT'
UNION ALL
SELECT EMPDATA.mgr,
EMPDATA.emp,
HARI_TMP.LEVEL+1
FROM EMPDATA,HARI_TMP
WHERE HARI_TMP.emp=EMPDATA.mgr
AND HARI_TMP.LEVEL<10
)
SELECT *
FROM HARI_TMP
ORDER BY 3,1,2;
and it returns the following result
1,2
2,3
2,4
4,5
4,6
I'm working on the query you had sent me.
Thanks,
Hari
Hari
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 2 days ago @ 3:41:05 AM
Posts: 522,
Visits: 264
|
|
Hi Hari,
i don't know your data, but it looks like you want to calculate the Transitive Closure, i.e. all existing paths.
For all rows it's probably:
WITH RECURSIVE HARI_TMP
(mgr,emp,LEVEL) AS
(
SELECT mgr,emp,0
FROM EMPDATA
UNION ALL
SELECT HARI_TMP.mgr,
EMPDATA.emp,
HARI_TMP.LEVEL+1
FROM EMPDATA,HARI_TMP
WHERE HARI_TMP.emp=EMPDATA.mgr
AND HARI_TMP.LEVEL<10
)
SELECT distinct (?) mgr,emp
FROM HARI_TMP
ORDER BY 1,2;
For a given start row (JOB_TITLE='PRESIDENT') i'd calculate a path and the join on it:
WITH RECURSIVE HARI_TMP
(mgr,emp,LEVEL, path) AS
(
SELECT mgr,emp,0,cast(emp as varchar(500))
FROM EMPDATA
WHERE JOB_TITLE='PRESIDENT'
UNION ALL
SELECT EMPDATA.mgr,
EMPDATA.emp,
HARI_TMP.LEVEL+1,
path || '.' || trim(empdata.emp)
FROM EMPDATA,HARI_TMP
WHERE HARI_TMP.emp=EMPDATA.mgr
AND HARI_TMP.LEVEL<10
)
SELECT t1.emp,t2.emp
FROM HARI_TMP t1 , HARI_TMP t2
where t2.path like '%' || t1.path || '%'
and t1.emp <> t2.emp
ORDER BY 1,2;
Dieter
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/7/2008 6:20:30 PM
Posts: 136,
Visits: 11
|
|
Dieter
I have not used much of recursive SQL's. But from what I came to know from manuals it may have its performance penalties (redistribution and other TD stuffs) if we do iterate thru a non-PI column (I believe).
I guess we may have to weigh and measure consideration before we trying this exciting features.
Vinay
|
|
|
|