求一oracle树数据sql
有个表A中有数据如下
gfid gfname
01档案查阅
01.01档案阅览
01.01.1导出
01.01.2打印
01.01.3收藏
01.02目录浏览
01.03档案查询
01.04全文检索
02档案借阅
02.01借阅办理
02.01.1借出
02.01.2归还
………………
怎么读出来生成符合树控件的包含父id数据
gfid gfname parentId
01档案查阅 0
01.01档案阅览 01
01.01.1导出 01.01
01.01.2打印 01.01
01.01.3收藏 01.01
01.02目录浏览 01
01.03档案查询 01
01.04全文检索 01
02档案借阅 0
02.01借阅办理 02
02.01.1借出 02.01
02.01.2归还 02.01
………………
已有的数据，不能改数据库结构了，只能自己补齐先天的不足。高手指点一下谢谢。
[解决办法]
case instr(gfid, '.', -1, 1) when 0 then '0' else substr(gfid, 0,instr(gfid, '.', -1, 1)-1) end as parentid
=>
decode(instr(gfid, '.', -1, 1),0 , '0' , substr(gfid, 0,instr(gfid, '.', -1, 1)-1)) as parentid
[解决办法]
WITH T AS(
SELECT '01'AS GFID,'档案查阅' AS GFNAME FROM DUAL UNION ALL
SELECT '01.01','档案阅览' AS GFNAME FROM DUAL UNION ALL
SELECT '01.01.1','导出' AS GFNAME FROM DUAL UNION ALL
SELECT '01.01.2','打印' AS GFNAME FROM DUAL UNION ALL
SELECT '01.01.3','收藏' AS GFNAME FROM DUAL UNION ALL
SELECT '01.02','目录浏览' AS GFNAME FROM DUAL UNION ALL
SELECT '01.03','档案查询' AS GFNAME FROM DUAL UNION ALL
SELECT '01.04','全文检索' AS GFNAME FROM DUAL UNION ALL
SELECT '02','档案借阅' AS GFNAME FROM DUAL UNION ALL
SELECT '02.01','借阅办理' AS GFNAME FROM DUAL UNION ALL
SELECT '02.01.1','借出' AS GFNAME FROM DUAL UNION ALL
SELECT '02.01.2','归还' AS GFNAME FROM DUAL
)
SELECT CASE
WHEN LENGTH(GFID) - LENGTH(REPLACE(GFID,'.')) = 0 THEN '0'
WHEN LENGTH(GFID) - LENGTH(REPLACE(GFID,'.')) = 1 THEN REGEXP_SUBSTR(GFID,'[^.]+',1,1)
WHEN LENGTH(GFID) - LENGTH(REPLACE(GFID,'.')) = 2 THEN REGEXP_SUBSTR(GFID,'[^.]+',1,2)
END AS PID,
CASE
WHEN LENGTH(GFID) - LENGTH(REPLACE(GFID,'.')) = 0 THEN GFID
WHEN LENGTH(GFID) - LENGTH(REPLACE(GFID,'.')) = 1 THEN REGEXP_SUBSTR(GFID,'[^.]+',1,2)
WHEN LENGTH(GFID) - LENGTH(REPLACE(GFID,'.')) = 2 THEN REGEXP_SUBSTR(GFID,'[^.]+',1,3)
END AS GID,
GFID,GFNAME
FROM T;
