Using a union with order by in a recurring request

[Laycee]

There's a request:

;with r (childId, parentId, childName, [level], [orderSequence], obj_id1)  
as 
(  
select de.id, de.parent_id, od.name, 0, cast(de.id as varchar(20)), obj_id  
from fstp.dbo.ogpo_dept od
inner join fstp.dbo.dept_employees de On de.obj_id = od.id and de.obj_type = 'dept'  
where obj_id = 75  
union all  
select de.id, de.parent_id, od.name, r.[level]+1, cast(r.orderSequence + '_' + cast(de.id as varchar) as varchar(20)), obj_id  
from fstp.dbo.ogpo_dept od
inner join fstp.dbo.dept_employees de On de.obj_id = od.id and de.obj_type = 'dept' 
inner join r on de.parent_id = r.childId  
)  
select right('         ', ([level]*3)) +childName

from r

order by r.orderSequence

Request is open. union all♪ I want to add a line through. union all

;with r (childId, parentId, childName, [level], [orderSequence], obj_id1)

as

(

select de.id, de.parent_id, od.name, 0, cast(de.id as varchar(20)), obj_id

from fstp.dbo.ogpo_dept od

inner join fstp.dbo.dept_employees de On de.obj_id = od.id and de.obj_type = 'dept'

where obj_id = 75

union all

select de.id, de.parent_id, od.name, r.[level]+1, cast(r.orderSequence + '_' + cast(de.id as varchar) as varchar(20)), obj_id

from fstp.dbo.ogpo_dept od

inner join fstp.dbo.dept_employees de On de.obj_id = od.id and de.obj_type = 'dept'

inner join r on de.parent_id = r.childId

)

select 'Все' as childName

union all

select right('         ', ([level]*3)) +childName

from r

order by r.orderSequence

But it's not working!

The multi-part identifier "r.orderSequence" could not be bound.

How do you sort it?

[Kadyn]

For example:

;with r (...)
as 
(  
...
),
r2 (num, orderSequence, childName) as
(
    select 1, NULL, 'All'
    union all 
    select 2, orderSequence, right(...) + childName
    from r
)
select childName
from r2
order by num, orderSequence
    ;

T.e. turning union all in the expression r2, add pole of sorting numwhich is constant 1 for element 'Все'and 2 for the rest of the data, further sorted by vapour num, orderSequence♪