>Hi all,
>Please can anyone suggest me a counter query in MS SQL for the one in Oracle.
>
>In Oracle I use the following Query to traverse the data from root to the last child.
>
>
>SELECT LPAD(' ',3*LVLCODE) || Name,
> LVLCODE, PRCODE
> FROM LEVELMST
> CONNECT BY PRIOR LVLCODE = PRCODE
> START WITH PRCODE = 0
>
>
>To traverse from child to root the query in Oracle is
>
>
>SELECT LPAD(' ',3*LVLCODE) || Name,
> LVLCODE, PRCODE
> FROM LEVELMST
> CONNECT BY LVLCODE = PRIOR PRCODE
> START WITH LVLCODE = 17
>
>
>Please help me with these queries.
>Thanks in advance.
In MSSQL 2000 there is no counterpart query. You have to craft your own, check 'expanding hierarchies' in books online.
In MSSQL 2005 you can use recursive CTE, check 'hierarchical queries [SQL Server]' in SQL server 2005 books online.
Cetin