Skip to content

Instantly share code, notes, and snippets.

@setap

setap/recurse.sql

Created March 4, 2015 14:30
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save setap/82140c0a6df1205733a6 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save setap/82140c0a6df1205733a6 to your computer and use it in GitHub Desktop.
Download ZIP
Oracle recusion select example
Raw
recurse.sql
SELECT count(incident_id) as NUM,
lvl2 as LVL2,
atc_dept_name as DEPT
FROM
(SELECT t1.incident_id,
t2.contact_name,
t2.dept_name,
SUBSTR(t3.Path, 27, 24) AS lvl2 ,
t3.Path,
t4.atc_dept_name
FROM incidentsm1 t1
LEFT OUTER JOIN contctsm1 t2 ON (t2.contact_name=t1.callback_contact)
LEFT OUTER JOIN
(SELECT dept_name,
SYS_CONNECT_BY_PATH(dept_name, '/') AS Path
FROM deptm1 START WITH parent IS NULL CONNECT BY
PRIOR dept_name=parent) t3 ON t3.dept_name=t2.dept_name
left outer join deptm1 t4 on (t4.dept_name=SUBSTR(t3.Path, 27, 24))
)
GROUP BY lvl2, atc_dept_name;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment