hierarchical select

hello,
i have a hierarchical select
something like this
with temp_containerCollect (id) as (
select c.id
from LOGI_T_CONT4REFCOLLECT c
where c.parent_id is null AND c.isActive like 'Y'
/~filter_refuseCollection: AND c.refusecollection_id =
{filter_refuseCollection} ~/
union all
select st.id from LOGI_T_CONT4REFCOLLECT st
inner join temp_containerCollect tst on st.parent_id = tst.id
where st.isActive like 'Y'
) select * from temp_containerCollect t
sometimes i get error with message The statement terminated. The maximum
recursion 100 has been exhausted before statement completion
i understand the message
i know solution with using hint OPTION (MAXRECURSION 2) but i don't wanna
use it
i'd like to set max recursion option globally for all hierarchical selects
so the question is: how to set max recursion option globally for database
instance?
thanks for all
TV
Hi Tomas
"Tomas Vojtech" wrote:

> hello,
> i have a hierarchical select
> something like this
> with temp_containerCollect (id) as (
> select c.id
> from LOGI_T_CONT4REFCOLLECT c
> where c.parent_id is null AND c.isActive like 'Y'
> /~filter_refuseCollection: AND c.refusecollection_id =
> {filter_refuseCollection} ~/
> union all
> select st.id from LOGI_T_CONT4REFCOLLECT st
> inner join temp_containerCollect tst on st.parent_id = tst.id
> where st.isActive like 'Y'
> ) select * from temp_containerCollect t
> sometimes i get error with message The statement terminated. The maximum
> recursion 100 has been exhausted before statement completion
> i understand the message
> i know solution with using hint OPTION (MAXRECURSION 2) but i don't wanna
> use it
> i'd like to set max recursion option globally for all hierarchical selects
> so the question is: how to set max recursion option globally for database
> instance?
> thanks for all
> TV
>
Although not quite what you asked the following suggestion has been logged at
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124653
where you can vote. If necessary you could add you specific request as well.
John

hierarchical select

hello,
i have a hierarchical select
something like this
with temp_containerCollect (id) as (
select c.id
from LOGI_T_CONT4REFCOLLECT c
where c.parent_id is null AND c.isActive like 'Y'
/~filter_refuseCollection: AND c.refusecollection_id =
{filter_refuseCollection} ~/
union all
select st.id from LOGI_T_CONT4REFCOLLECT st
inner join temp_containerCollect tst on st.parent_id = tst.id
where st.isActive like 'Y'
) select * from temp_containerCollect t
sometimes i get error with message The statement terminated. The maximum
recursion 100 has been exhausted before statement completion
i understand the message
i know solution with using hint OPTION (MAXRECURSION 2) but i don't wanna
use it
i'd like to set max recursion option globally for all hierarchical selects
so the question is: how to set max recursion option globally for database
instance?
thanks for all
TVHi Tomas
"Tomas Vojtech" wrote:

> hello,
> i have a hierarchical select
> something like this
> with temp_containerCollect (id) as (
> select c.id
> from LOGI_T_CONT4REFCOLLECT c
> where c.parent_id is null AND c.isActive like 'Y'
> /~filter_refuseCollection: AND c.refusecollection_id =
> {filter_refuseCollection} ~/
> union all
> select st.id from LOGI_T_CONT4REFCOLLECT st
> inner join temp_containerCollect tst on st.parent_id = tst.id
> where st.isActive like 'Y'
> ) select * from temp_containerCollect t
> sometimes i get error with message The statement terminated. The maximum
> recursion 100 has been exhausted before statement completion
> i understand the message
> i know solution with using hint OPTION (MAXRECURSION 2) but i don't wanna
> use it
> i'd like to set max recursion option globally for all hierarchical selects
> so the question is: how to set max recursion option globally for database
> instance?
> thanks for all
> TV
>
Although not quite what you asked the following suggestion has been logged a
t
https://connect.microsoft.com/SQLSe...=1246
53
where you can vote. If necessary you could add you specific request as well.
John

Hierarchical Resultset Sorting

I have a query like this

with TempCTE(id, Name, level, sortcol)
As
(
Select id, Name, 0 as level,
cast(cast( id AS BINARY(4)) as varbinary(100)) sortcol
from Table1
where id = 1

union all

Select id, Name, 0 as level,
cast(sortcol + cast( id AS BINARY(4)) as varbinary(100)) sortcol

from Table1 inner join TempCTE on TempCTE.id = Table1.parentid
)

select * from TempCTE order by sortcol

My problem is I want to sort this hierarchical resultset further on name like

aaa
--aaaa
--bbbb
--cccc
aaaaa
bbbbb
--dddd
aaaaa
bbbbb
bbb
--aaaa
--bbbb

Thanks

You need to basically add the appropriate columns in the ORDER BY clause. Based on your query above, you need to probably do ORDER BY sortcol, name. See below example based on a query using AdventureWorks sample tables:

WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT replicate('.', EmployeeLevel* 10) + right(replicate('0', 10) + cast(coalesce(ManagerID, 0) as varchar), 10) as Mgr
FROM DirectReports
ORDER BY EmployeeLevel, ManagerID, EmployeeID;
GO