I have a interesting problem, at least I think so. So, I have a table (codes_table) with the above structure (it is a tree menu) the tree is defined by two integers plus a dot pattern (always) like this:
COD_MENU NAME
01. Biggest Node
01.01. Sun of 01.
01.01.01. Sun of 01.01.
01.01.01.01. Sun of 01.01.01.
01.01.01.02. Sun of 01.01.01.
01.01.01.03. Sun of 01.01.01.
01.01.01.04. Sun of 01.01.01.
01.01.01.05. Sun of 01.01.01.
01.02. Sun of 01.
01.03. Sun of 01.
01.03.01. Sun of 01.03.
(etc...)
What I need is to generate insert scripts to a new table with a structure with numeric IDs with foreing key to its father putting the actual COD_MENU as name on the new structure like this:
ID FATHER_ID NAME
1 NULL 01.
2 1 01.01.
3 2 01.01.01.
4 3 01.01.01.01.
5 3 01.01.01.02.
6 3 01.01.01.03.
7 3 01.01.01.04.
8 3 01.01.01.05.
9 1 01.02.
10 1 01.03.
11 10 01.03.01.
I already did this in a plsql block code with a recursive procedure. Above is my real code for reference.
declare
auxId integer;
procedure findNodes( pCod varchar2, pCurrId in out integer, pFatherId in integer ) is
ctHasSuns integer;
father varchar2(20);
idFhtTmp integer;
begin
idFhtTmp := pFatherId;
if idFhtTmp is null then
father := 'null';
else
father := idFhtTmp;
end if;
ctHasSuns := 0;
SELECT count(cod_menu) into ctHasSuns FROM codes_table where SUBSTR (cod_menu, 1,LENGTH (cod_menu) - 3) = pCod and rownum < 10;
if (ctHasSuns > 0 or pCurrId = 1) then
dbms_output.put_line ('insert into newtable ( id, idfather, menu ) values ( '||pCurrId||','||father||', '''||pCod||''' );');
idFhtTmp := pCurrId;
for cHasSuns in ( SELECT cod_menu FROM codes_table where SUBSTR (cod_menu, 1,LENGTH (cod_menu) - 3) = pCod and rownum < 10 order by cod_menu) loop
pCurrId := pCurrId + 1;
findNodes( cHasSuns.cod_menu, pCurrId, idFhtTmp );
end loop;
else
dbms_output.put_line ('insert into newtable ( id, idfather, menu ) values ( '||pCurrId||','||father||', '''||pCod||''' );');
end if;
end;
begin
auxId := 1;
findNodes( '01.', auxId, null );
end;
What I'm trying to do is acomplish that in a single query using CONNECT BY / START WITH / ROWNUM and LEVEL statments and usign joins with the same table if necessary. I tryied a lot of things and couldn't came out with a solution. This is just for curiosity.
The closest thing I could do is the select above but I just have the ID for the father not the suns
WITH q AS (
select rownum id, father from (
select a.noh father, count(*)
from (select cod_menu noh, substr( cod_menu, 1, length(cod_menu)-3) as nofather from codes_table) a,
(select cod_menu noh, substr( cod_menu, 1, length(cod_menu)-3) as nofather from codes_table) b
where a.noh = b.nofather
group by a.noh
having count(*) > 0
order by a.noh)
),
b as (
SELECT cod_menu filho, father
FROM (select cod_menu, substr( cod_menu, 1, length(cod_menu)-3) as father from codes_table)
START WITH father is null
CONNECT BY prior cod_menu = father
ORDER BY cod_menu
)
SELECT * FROM q, b where q.father = b.father;
Thank you for your time and help.
Try below solution:
CREATE TABLE codes_table (
cod_menu VARCHAR2(40),
name VARCHAR2(40)
);
INSERT INTO codes_table VALUES ('01.', 'Biggest Node');
INSERT INTO codes_table VALUES ('01.01.', 'Sun of 01.');
INSERT INTO codes_table VALUES ('01.01.01.', 'Sun of 01.01.');
INSERT INTO codes_table VALUES ('01.01.01.01.', 'Sun of 01.01.01.');
INSERT INTO codes_table VALUES ('01.01.01.02.', 'Sun of 01.01.01.');
INSERT INTO codes_table VALUES ('01.01.01.03.', 'Sun of 01.01.01.');
INSERT INTO codes_table VALUES ('01.01.01.04.', 'Sun of 01.01.01.');
INSERT INTO codes_table VALUES ('01.01.01.05.', 'Sun of 01.01.01.');
INSERT INTO codes_table VALUES ('01.02.', 'Sun of 01.');
INSERT INTO codes_table VALUES ('01.03.', 'Sun of 01.');
INSERT INTO codes_table VALUES ('01.03.01.', 'Sun of 01.03.');
COMMIT;
SELECT
id,
PRIOR id AS father_id,
cod_menu AS name
FROM (
SELECT
cod_menu,
name,
row_number() OVER (ORDER BY cod_menu) AS id
FROM
codes_table
)
START WITH cod_menu = '01.'
CONNECT BY SUBSTR(cod_menu, 1, LENGTH(cod_menu) - 3) = PRIOR cod_menu
;
Check at SQLFiddle: Fiddle with it