Good afternoon, I can not figure out what the problem is, trying to build a tree. The request itself works only until the time when there are no more than 10 Structural Subdivisions when everything is less, everything is OK, when everything moves more, could you tell me what I'm doing wrong? Here is my query and table schema.

WITH RECURSIVE Rec(DeptKod, PKod, DeptName, Path, Level) AS( SELECT DeptKod, PKod, cast(DeptName AS VARCHAR) AS DeptName, cast(DeptKod AS VARCHAR) AS Path,1 FROM Structural_Subdivision WHERE PKod IS NULL UNION SELECT D.DeptKod, D.PKod, lpad(' ', 3*level) || D.DeptName,Path || cast(D.DeptKod AS VARCHAR), Level+1 FROM Structural_Subdivision D INNER JOIN Rec R ON (D.PKod = R.DeptKod)) SELECT DeptKod, DeptName FROM Rec ORDER BY Path;

enter image description here

  • What does it mean, “If you are moving away,” if you are talking about the DeptName field, maybe your font is not monospaced when outputting - Mike
  • here are the links. in the first link 9 divisions and all the rules work, and in the second 10 divisions and does not work as it should. 1. sqlfiddle.com/#!15/19f075/1 2. sqlfiddle.com/#!15/169fb/1 - Sergey Zavada
  • so maybe nothing moves out, just sorting breaks, because 10 when sorting a line goes right after 1 and before 2. to avoid this, you need to do a fixed length id in the line on which the sort is, i.e. so DeptKod = 1 would look like 00001, i.e. do lpad zero to the required number of characters - Mike
  • sqlfiddle.com/#!15/169fb/3 - Mike
  • Mike, thanks helped) - Sergey Zavada

0