Teradata equivalent with recursive

Database
N/A

Teradata equivalent with recursive

hi all,
plz help me to solve this query.

select emp_id, emp_DB, level emp_level
from emp start with emp_id = 111
connect by Prior emp_DB = emp_id

The output i want is :
http://www.adp-gmbh.ch/ora/sql/connect_by.html
please see the above site and suggest me.

Thanks in advance.
4 REPLIES
N/A

Re: Teradata equivalent with recursive

Hi,

for recursion - a very good article
http://www.teradata.com/tdmo/v06n03/Tech2Tech/InsidersWarehouse/OddballSQLTricks.aspx

Re: Teradata equivalent with recursive

I think what you are looking for is something like this ....

WITH RECURSIVE RECEMP(EMP_ID, EMP_DB, LEVEL)
AS
(

SELECT EMP_ID, EMP_DB, 1(INTEGER)
FROM EMP
WHERE EMP_ID = 11

UNION ALL

SELECT E.EMP_ID, E.EMP_DB, R.LEVEL+1
FROM EMP E INNER JOIN RECEMP R
ON E.EMP_ID = R.EMP_DB

)
SELECT EMP_ID, EMP_DB, LEVEL
FROM RECEMP
;

N/A

Re: Teradata equivalent with recursive

Thanks all. it worked.
Teradata Employee

Re: Teradata equivalent with recursive

(
SELECT EMP_ID, EMP_DB, 1(INTEGER)
FROM EMP
WHERE EMP_ID = 11
UNION ALL
SELECT E.EMP_ID, E.EMP_DB, R.LEVEL+1
FROM EMP E INNER JOIN RECEMP R
ON E.EMP_ID = R.EMP_DB
)
SELECT EMP_ID, EMP_DB, LEVEL
FROM RECEMP
;

Trying to understand recursion, and I think I basically have it, but I am confused by the presence of the 1(INTEGER) in the 1st SELECT. Is this simply saying that this employee_id is the highest grade/band/level? If so, why would we not simply return the actual grade/band/level? I am pretty sure I am not understanding that part.

Also, as I see this, we are starting at a high grade and then finding those employees under EMP_ID 11. What if I wanted to start with, say, a contact centre advisor and work my way up to the MD through the hierarchy?

Thanks!

Andrew Livingston