I need a recursive query to get a calculated field in oracle -
i'm not sure best approach result i'm looking for. in oracle 10g database i'm trying query users , produce calculated field each row has supervisor if supervisor type u. if not need walk tree until find 1 is. query might need recurse multiple levels.
so employee table this:
employee table +-------+----------+--------------+---------------+ | empno | empgroup | user | supervisor_no | +-------+----------+--------------+---------------+ | 1 | e | joe schmo | 4 | | 2 | e | mark marin | 3 | | 3 | u | reed patter | 7 | | 4 | e | martin price | 7 | | 7 | u | mary wengert | | +-------+----------+--------------+---------------+ i'd see results manager_no calculated field
results +-------+----------+--------------+---------------+------------+ | empno | empgroup | user | supervisor_no | manager_no | +-------+----------+--------------+---------------+------------+ | 1 | e | joe schmo | 4 | 7 | | 2 | e | mark marin | 3 | 3 | | 3 | u | reed patter | 7 | 7 | | 4 | e | martin price | 7 | 7 | | 7 | u | mary wengert | | | +-------+----------+--------------+---------------+------------+ ok have been asked have tried. not saying way has done if has better suggestion i'm ears i'm trying achieve.
i envisioned 2 parts. main query gets results
select em.empno, em.empgroup, em.user, em.supervisor, (my subquery) manager_no employee em query:
select * employee em connect prior supervisor_no = empno start empno = 1 [results][2]:
| empno | empgroup | username | supervisor_no | --------------------------------------------------- | 1 | e | joe schmo | 4 | | 4 | e | martin price | 7 | | 7 | u | mary wengert | (null) | ok, found way filter on group might work not sure if efficient route go.
select empno ( select empno employee em connect prior supervisor_no = empno start empno = 1 order level) d d.empgroup = 'u' , rownum =1 i created fiddle if helps. http://www.sqlfiddle.com/#!4/c8805/4
well, question became quite challenge me, didn't give , got using sql , built-in functions (shouldn't work slowly, , quite indexable needed):
select distinct empno, empgroup, username, supervisor_no, manager_no ( select e.*, decode( instr( sys_connect_by_path(empgroup, '/'), 'u/', -1 ), 0, null, substr( sys_connect_by_path(empno, '/'), instr( sys_connect_by_path(empno, '/'), '/', 1, length( substr( sys_connect_by_path(empgroup, '/'), 1, instr( sys_connect_by_path(empgroup, '/'), 'u/', -1 ) ) ) - length( replace( substr( sys_connect_by_path(empgroup, '/'), 1, instr( sys_connect_by_path(empgroup, '/'), 'u/', -1 ) ), '/' ) ) ) + 1, instr( sys_connect_by_path(empno, '/'), '/', 1, length( substr( sys_connect_by_path(empgroup, '/'), 1, instr( sys_connect_by_path(empgroup, '/'), 'u/', -1 ) ) ) - length( replace( substr( sys_connect_by_path(empgroup, '/'), 1, instr( sys_connect_by_path(empgroup, '/'), 'u/', -1 ) ), '/' ) ) + 1 ) - instr( sys_connect_by_path(empno, '/'), '/', 1, length( substr( sys_connect_by_path(empgroup, '/'), 1, instr( sys_connect_by_path(empgroup, '/'), 'u/', -1 ) ) ) - length( replace( substr( sys_connect_by_path(empgroup, '/'), 1, instr( sys_connect_by_path(empgroup, '/'), 'u/', -1 ) ), '/' ) ) ) - 1 ) ) manager_no employee e connect prior empno = supervisor_no ) manager_no not null or supervisor_no null order empno; the sql fiddle query: http://www.sqlfiddle.com/#!4/c8805/27/0
update: when got in morning, realized can done easier , query became more readable, here go:
select empno, empgroup, username, supervisor_no, null manager_no employee supervisor_no null union select empno, empgroup, username, supervisor_no, substr(ep, 2, instr(ep, '/', 2)-2) manager_no ( select sys_connect_by_path(empgroup, '/') gp, sys_connect_by_path(empno, '/') ep, e.* employee e connect prior empno = supervisor_no ) e substr(gp, 1, 3) = '/u/' , (length(gp) - length(replace(gp, 'u/'))) = length('u/') order empno; and works! query above. sql fiddle it: http://www.sqlfiddle.com/#!4/c8805/54/0
enjoy!
Comments
Post a Comment