Recursive query

Database

Recursive query

Hi Experts,

My table is like

EMPLOYEE_ID                 HR_ID

100                                    200

101                                    200

200                                    999

201                                    999

So requiement is if I want to see what are employee_id are available under HR_id 999 then it should give

EMPLOYEE_id              HR_id

100                              200

101                              201

100 (Via HR_Id 200)     999

101 (Via HR_Id 200)     999

200 (Direct)                  999

201 (Direct)                  999

Please help

6 REPLIES

Re: Recursive query

Hi Arindam,

Yes a recursive query will do the work.

Have you tried with a self join of the table and then concatenate to get the desired result. It is a hierarchical form of query. I am going to office and hope I have time to look at this.

Cheers,

Raja

Re: Recursive query

Hi Raja, Anyone

can anyone help on this?

Thanks,

Arindam

Teradata Employee

Re: Recursive query

Try,

WITH RECURSIVE REC_TBL ( emp_id, boss_id ) AS
(SELECT
emp_id, (boss_id) AS boss_id FROM employee_tbl
UNION ALL
SELECT a.emp_id, b.boss_id
FROM REC_TBL A , employee_tbl b
WHERE a.boss_id = b.emp_id

)
SELECT
*
FROM REC_TBL

Re: Recursive query

Hi Arindam,

I dont have access to Teradata. I have one free oracle online server. So I tried with that for solutions, since I work on other Databases too. I dont know if I can have a Teradata online server.

How about the solution given above with recursive? If it does not work , then we  can think of cross join and then join the result with the parent query.

Can anyone let me know if I  have any free Teradata online server?

Thanks and regards,

Raja

Re: Recursive query

Hi Raja,

If a query runs fine in Oracle then there is 99% chance that it will run on TD also. I will let u know with the results.Thanks, 

Arindam

Re: Recursive query

Hi Gerardo,

Thanks, I will check and will let u know the result.