Hello again. There is a table:

create table test_table ( id integer not null, name char(1) not null, parent_id integer, sum integer not null); INSERT INTO test_table VALUES(1, 'A', null, 300); INSERT INTO test_table VALUES(11, 'B', 1, 2340); INSERT INTO test_table VALUES(111, 'C', 11, 3200); INSERT INTO test_table VALUES(22, 'D', 1, 7540); INSERT INTO test_table VALUES(2, 'E', null, 6300); INSERT INTO test_table VALUES(3, 'F', 2, 8600); INSERT INTO test_table VALUES(101, 'G', 3, 3800); INSERT INTO test_table VALUES(102, 'H', 3, 9700);

And there is a code:

 SELECT lpad(' ', 3*level)||name as Tree FROM test_table A START WITH parent_id is null CONNECT BY PRIOR id = parent_id;

How to do the same without using CONNECT BY (through WITH )?

So, I found an application using WITH ( here ), but it just doesn't work - it shows the name of the CTE and says that the key word is missing:

 WITH RECURSIVE Rec (id, parent_id, name) AS ( SELECT id, parent_id, name FROM test_table UNION ALL SELECT Rec.id, Rec.parent_id, Rec.name FROM Rec, test_table WHERE Rec.id = test_table.parent_id ) SELECT * FROM Rec WHERE parent_id is null;
  • And how have you tried it yourself? - Viktorov
  • Well, for such questions, Google is perfectly responsible for askingtom.oracle.com/pls/apex ... and even reading the text is not necessary. code examples, see all - Mike
  • Apparently, looking for the wrong one) - daydark
  • Perhaps the correct terminology solves. I asked google "oracle recursive cte" - Mike
  • but level padding requires connect by - daydark

1 answer 1

I will give an example of how I would rewrite your query for MS SQL (there is just no CONNECT, there is only a recursive CTE). Only the function for multiplying spaces in the beginning did not change - in MS SQL it is different. Try it out if that would suit you:

 with rec (id, parent_id, name, lvl) as ( select id, parent_id, name, 0 from test_table where parent_id is null union all select tt.id, tt.parent_id, tt.name, rec.lvl + 1 from test_table tt inner join rec on tt.parent_id = rec.id ) SELECT lpad(' ', 3*lvl)||name as Tree from rec
  • Unfortunately. If we add a level to the list of columns for the CTE, Oracle will not know where to look for it, because in my table it is not, CONNECT BY, too. So, you need to clean it. Removed. next line select id, parent_id, name, 0 as level - error "The FROM keyword was not found where it was expected. Okay, change the level to lvl in all places, then another error is issued" The number of column names WITH does not match the number of elements in the select list, add lvl to the CTE columns selection - “Cycle detected during recursive query“ Everything closed circle) - daydark
  • @daydark I checked this request. if in the recursive part to the columns, add that they are from the tt table and replace the word level (reserved for connect by in Oracle), for example, with lvl - then everything works fine - Mike
  • Darn. I would also know that this tt exists - daydark
  • @daydark well, so they called her themselves this from test_table tt - Mike
  • @daydark, yes, my mistake, tt is an alias to your own table for ease of writing. Mike, thanks for editing. - minamoto