title: Change select with connect by prior from Oracle to SQL Server
body: <p>Hello I have this part of a view in an Oracle database and I must change it on Microsoft Sql Server.</p>
  
  <pre><code>with V_LOCHIERARHY_N
  (nr, nivel, location, parent, systemid, siteid, orgid, count_a, count_wo, children)
  AS
  SELECT     LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || l.LOCATION nivel,
                LOCATION, PARENT, systemid, siteid, orgid,
               (SELECT COUNT (a.ancestor)
               FROM locancestor a
              WHERE a.LOCATION = l.LOCATION AND a.siteid = l.siteid),
                    NVL (COUNT (w.wonum), 0)
               FROM maximo.workorder w
  
              WHERE  (    w.reportdate &gt;
                            TO_TIMESTAMP ('2006-06-19 00:00:01',
                                          'YYYY-MM-DD HH24:MI:SS.FF'
                                         )
                     AND w.istask = 0
                     AND w.worktype &lt;&gt; 'P'
                     AND w.LOCATION = l.LOCATION
                    )
                AND w.status &lt;&gt; 'CAN'),
            l.children
       FROM lochierarchy l
      START WITH l.LOCATION = 'StartPoint'
     CONNECT BY PRIOR l.LOCATION = l.PARENT AND l.siteid = 'SiteTest'
  </code></pre>
  
  <p>What I need from this script is to return all the children of a given entry (the description of the children which can be found in locations table).</p>
  
  <p>I have a table with next columns:</p>
  
  <pre><code>Location Parent      Systemid Children Siteid Origid Lochierarchyid
  A001     StartPoint  Primary  2        SiteTest    X      106372
  A002     A001        Primary  2        SiteTest    X      105472
  A003     A002        Primary  0        SiteTest    X      98654
  A004     A002        Primary  1        SiteTest    X      875543
  A004B    A004        Primary  0        SiteTest    X      443216
  B005     StartPoint  Primary  0        SiteTest    X      544321
  </code></pre>
  
  <p>For example for given entry A001 will return</p>
  
  <hr>
  
  <pre><code>A002    
   A003     
   A004
    A004B     
  B005 
  </code></pre>
  
  <p>I have made this view below but I don't know how to integrate it with the first one. Also it doesn't return me the list in the corectly order </p>
  
  <pre><code>Parent  
   Children 1 of parent
    Children a of children 1
    children b of children 1
   children 2 of parent
    children a1 of children 2 and so on.
  </code></pre>
  
  <hr>
  
  <pre><code> WITH testCTE AS
  (
       SELECT l.parent, l.location as child, l.location, l.lochierarchyid
       FROM lochierarchy l
       where location='SecondLocation' --and siteid='SiteTest'
         UNION ALL
       SELECT c.Parent, l.parent, l.location, l.lochierarchyid
       FROM lochierarchy l 
      INNER JOIN testCTE c ON l.parent = c.location
   )
    SELECT  *
    FROM testCTE c
    order BY c.parent,child asc
   ;
  </code></pre>
  
  <p>Can please someone help me? :)</p>