Using a union with order by in a recurring request



  • 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?



  • 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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2