23 Thursday May 2013
Posted in Tipps and Tricks
Tags
How can we convert a string including seperators into it’s token using only SQL?
Easiest way is to make use of recursive query techniques and regular expression as shown below:
WITH data (string) AS (SELECT 'MEIER,HUBER,MUELLER,KUHN,,KOLLER' FROM dual)
SELECT REGEXP_SUBSTR (data.string, '[^,]+', 1, ROWNUM) element_string
FROM data
CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (DATA.STRING, '[^,]+')) + 1
/
ELEMENT_STRING
--------------
MEIER
HUBER
MUELLER
KUHN
KOLLER
(null)
23 Thursday May 2013
Posted in ORACLE 11g Release 2
Tags
ORACLE 11g release 2 introduced the recursive WITH clause as an alternative to the well known CONNECT BY clause.
This Blog entry shows how to implement all the CONNECT BY features using the new recursive WITH clause.
Using CONNECT BY the pseudo column CONNECT_BY_ISLEAF can be used to determine whether a node is a leaf node (has no children) or an inner node. The value of the pseudo column CONNECT_BY_ISLEAF is “0” for an inner node and “1” for leaf nodes.
SELECT LEVEL, ename, CONNECT_BY_ISLEAF leaf, empno, mgr
FROM emp e
WHERE deptno = 10
CONNECT BY PRIOR empno = mgr
START WITH mgr is NULL
ORDER SIBLINGS BY ename
/
LEVEL ENAME LEAF EMPNO MGR
---------- ---------- ---------- ---------- ----------
1 KING 0 7839
2 CLARK 0 7782 7839
3 MILLER 1 7934 7782
Recursive WITH clause does not offer a built-in function that provides this information. Easiest way to get the information is using a subquery (in-/outside the WITH clause) that counts the number of child nodes.
WITH emps (ename, empno, mgr, lvl)
AS (SELECT ename, empno, mgr, 1 AS lvl
FROM emp
WHERE mgr IS NULL
UNION ALL
SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl
FROM emps m
INNER JOIN emp e ON (e.mgr = m.empno)
WHERE e.deptno = 10)
SEARCH DEPTH FIRST BY ename SET sorting
SELECT e.lvl, e.ename
, 1 - LEAST((SELECT COUNT(*) FROM emp x WHERE x.mgr = e.empno),1) leaf
, e.empno, e.mgr
FROM emps e
ORDER BY e.sorting
/
LEVEL ENAME LEAF EMPNO MGR
---------- ---------- ---------- ---------- ----------
1 KING 0 7839
2 CLARK 0 7782 7839
3 MILLER 1 7934 7782
If the SEARCH clause uses DEPTH_FIRST, the analytic function LEAD can be used to determine whether the column LVL of next row in the result set has a higher value than the current row. If so, the current row is not a leaf otherwise the current row is a leaf.
WITH emps (ename, empno, mgr, lvl)
AS (SELECT ename, empno, mgr, 1 AS lvl
FROM emp
WHERE mgr IS NULL
UNION ALL
SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl
FROM emps m
INNER JOIN emp e ON (e.mgr = m.empno)
WHERE e.deptno = 10)
SEARCH DEPTH FIRST BY ename SET sortingSELECT lvl
,ename
,CASE
WHEN lvl - LEAD(lvl) OVER (ORDER BY sorting) < 0
THEN 0
ELSE 1
END AS leaf
,empno
,mgr
FROM emps
ORDER BY sorting
/
LVL ENAME LEAF EMPNO MGR
---------- ---------- ---------- ---------- ----------
1 KING 0 7839
2 CLARK 0 7782 7839
3 MILLER 1 7934 7782
03 Thursday Nov 2011
Posted in ORACLE 11g Release 2
Tags
ORACLE 11g release 2 introduced the recursive WITH clause as an alternative to the well known CONNECT BY clause.
This Blog entry shows how to implement all the CONNECT BY features using the new recursive WITH clause.
If the data contains a cylce the query would run indefinitely. ORACLE detects these situations and lets the query fail.
UPDATE emp
SET mgr = 7499
WHERE empno = 7839
/
SELECT LEVEL, e.ename
FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
START WITH e.empno = 7839
ORDER SIBLINGS BY e.ename
/
ERROR:
ORA-01436: CONNECT BY loop in user data
Recursive suquery factoring throws a different error code with a similar message.
WITH r_emps (ename, empno, mgr, lvl, path, root)
AS (SELECT e.ename, e.empno, e.mgr
, 1 as lvl
, '/' || e.ename AS path
, e.ename AS root
FROM emp e
WHERE e.empno = 7839
UNION ALL
SELECT e.ename, e.empno, e.mgr
, m.lvl + 1 AS lvl
, m.path || '/' || e.ename AS path
, m.root
FROM emp e
INNER JOIN r_emps m ON (m.empno = e.mgr))
SEARCH DEPTH FIRST BY ename SET sorting
SELECT e.lvl, e.ename, e.empno, e.mgr, e.path, e.root
FROM r_emps e
ORDER BY e.sorting
/
ERROR at line ..:
ORA-32044: cycle detected while executing recursive WITH query
Since ORACLE 10g the CONNECT BY clause knows the NOCYLCE attribute as well as the CONNECT_BY_ISCYCLE pseudo column to ignore cycles and detect nodes with a connection to a node already included in the result set (cycle).
SELECT LEVEL
, CONNECT_BY_ISCYCLE AS CYCLE
, e.ename
, SYS_CONNECT_BY_PATH(e.ename, '/') AS path
, e.mgr
FROM emp e
CONNECT BY NOCYCLE PRIOR e.empno = e.mgr
START WITH e.empno = 7839
ORDER SIBLINGS BY e.ename
/
LEVEL CYCLE ENAME PATH MGR
----- ----- -------- --------------------- ----
1 0 KING /KING 7499
2 0 BLAKE /KING/BLAKE 7839
3 1 ALLEN /KING/BLAKE/ALLEN 7698
3 0 JAMES /KING/BLAKE/JAMES 7698
3 0 MARTIN /KING/BLAKE/MARTIN 7698
3 0 TURNER /KING/BLAKE/TURNER 7698
3 0 WARD /KING/BLAKE/WARD 7698
2 0 CLARK /KING/CLARK 7839
3 0 MILLER /KING/CLARK/MILLER 7782
2 0 JONES /KING/JONES 7839
3 0 FORD /KING/JONES/FORD 7566
4 0 SMITH /KING/JONES/FORD/SMITH 7902
3 0 SCOTT /KING/JONES/SCOTT 7566
The cycle was detected at node ALLAN (CONNECT_BY_ISCYCLE returned the value 1) and further processing of this branch is stopped.
The recursive WITH clause also offers a clause to handle cycles.
WITH r_emps (ename, empno, mgr, lvl, path)
AS (SELECT e.ename, e.empno, e.mgr, 1 AS lvl, '/' || e.ename AS path
FROM emp e
WHERE e.empno = 7839
UNION ALL
SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl, m.path || '/' || e.ename AS path
FROM r_emps m
INNER JOIN emp e ON (e.mgr = m.empno))
SEARCH DEPTH FIRST BY ename SET SORTING
CYCLE empno SET is_cycle TO 1 DEFAULT 0
SELECT e.lvl AS LEVEL, is_cycle AS CYCLE, e.ename, e.path ,e.mgr
FROM r_emps e
ORDER BY e.sorting
/
LEVEL CYCLE ENAME PATH MGR
----- ----- --------- ----------------------- ----
1 0 KING /KING 7499
2 0 BLAKE /KING/BLAKE 7839
3 0 ALLEN /KING/BLAKE/ALLEN 7698
4 1 KING /KING/BLAKE/ALLEN/KING 7499
3 0 JAMES /KING/BLAKE/JAMES 7698
3 0 MARTIN /KING/BLAKE/MARTIN 7698
3 0 TURNER /KING/BLAKE/TURNER 7698
3 0 WARD /KING/BLAKE/WARD 7698
2 0 CLARK /KING/CLARK 7839
3 0 MILLER /KING/CLARK/MILLER 7782
2 0 JONES /KING/JONES 7839
3 0 FORD /KING/JONES/FORD 7566
4 0 SMITH /KING/JONES/FORD/SMITH 7902
3 0 SCOTT /KING/JONES/SCOTT 7566
4 0 ADAMS /KING/JONES/SCOTT/ADAMS 7788
The difference between CONNECT BY and RECURSIVE WITH cycle detection is that with RECURSIVE WITH the cycle is decteted after the next recursion level wass processed. The erroneous node is repeated and the cycle flag is set one level lower than the CONNECT_BY_ISCYCLE pseudo column.
19 Monday Sep 2011
Posted in ORACLE 11g Release 2
Tags
ORACLE 11g release 2 introduced the recursive WITH clause as an alternative to the well known CONNECT BY clause.
This Blog entry shows how to implement all the CONNECT BY features using the new recursive WITH clause.
CONNECT BY clause knows the CONNECT_BY_ROOT operator which returns the root(s) of a hierarchy. Furthermore the SYS_CONNECT_BY_PATH function may be used to get a path from the root to the current element within the hierarchy:
SELECT e.ename, e.empno, e.mgr
,SYS_CONNECT_BY_PATH(e.ename,'/') AS path
,CONNECT_BY_ROOT e.ename AS root
FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
START WITH e.mgr IS NULL
ORDER SIBLINGS BY e.ename
/
ENAME EMPNO MGR PATH ROOT
------- ----- ---- ----------------------- ----
KING 7839 /KING KING
BLAKE 7698 7839 /KING/BLAKE KING
ALLEN 7499 7698 /KING/BLAKE/ALLEN KING
JAMES 7900 7698 /KING/BLAKE/JAMES KING
MARTIN 7654 7698 /KING/BLAKE/MARTIN KING
TURNER 7844 7698 /KING/BLAKE/TURNER KING
WARD 7521 7698 /KING/BLAKE/WARD KING
CLARK 7782 7839 /KING/CLARK KING
MILLER 7934 7782 /KING/CLARK/MILLER KING
JONES 7566 7839 /KING/JONES KING
FORD 7902 7566 /KING/JONES/FORD KING
SMITH 7369 7902 /KING/JONES/FORD/SMITH KING
SCOTT 7788 7566 /KING/JONES/SCOTT KING
ADAMS 7876 7788 /KING/JONES/SCOTT/ADAMS KING
Same result using the recursive WITH clause:
WITH r_emps (ename, empno, mgr, path, root)
AS (SELECT e.ename, e.empno, e.mgr
, '/' || e.ename AS path
, e.ename AS root
FROM emp e
WHERE e.mgr IS NULL
UNION ALL
SELECT e.ename, e.empno, e.mgr
, m.path || '/' || e.ename AS path
, m.root
FROM emp e
INNER JOIN r_emps m ON (m.empno = e.mgr))
SEARCH DEPTH FIRST BY ename SET sorting
SELECT e.ename, e.empno, e.mgr, e.path, e.root
FROM r_emps e
ORDER BY e.sorting
/
Path and root are set to ename in the anchor query. While the path is enlarged along the hierarchy by adding separator and path the root is simply passed all the way down to the leafs.
19 Monday Sep 2011
Posted in ORACLE 11g Release 2, Uncategorized
Tags
ORACLE 11g release 2 introduced the recursive WITH clause as an alternative to the well known CONNECT BY clause.
This Blog entries show how to implement all the CONNECT BY features using the new recursive WITH clause.
CONNECT BY clause to show all employees starting with KING (who has no manager [mgr IS NULL]):
SELECT e.ename, e.empno, e.mgr
FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
START WITH e.mgr IS NULL
/
Same result using the recursive WITH clause:
WITH r_emps (ename, empno, mgr) AS (SELECT e.ename, e.empno, e.mgr
FROM emp e
WHERE e.mgr IS NULL
UNION ALL
SELECT e.ename, e.empno, e.mgr
FROM emp e
INNER JOIN r_emps m ON (m.empno = e.mgr))
SELECT e.ename, e.empno, e.mgr
FROM r_emps e
/
The first query block (anchor member) of the recursive WITH clause defines the root(s) of the hierarchy, the second the recursion. In the second block, we can see a join between the
anchor member and the emp table which is pretty much the same as the CONNECT BY clause in the traditional approach.
CONNECT BY queries often use the LEVEL pseudo column to format the output.
SELECT LEVEL, LPAD(' ',2*(LEVEL-1)) || e.ename AS ename
, e.empno, e.mgr
FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
START WITH e.mgr IS NULL
/
The same can be achieved using the following recursive WITH query:
WITH r_emps (ename, empno, mgr, lvl)
AS (SELECT e.ename, e.empno, e.mgr, 1 AS lvl
FROM emp e
WHERE e.mgr IS NULL
UNION ALL
SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl
FROM emp e
INNER JOIN r_emps m ON (m.empno = e.mgr))
SELECT lvl, LPAD(' ',2*(lvl-1)) || e.ename AS ename
,e.empno, e.mgr
FROM r_emps e
/
To simulate the LEVEL pseudo column using the recursive WITH clause, one has to add a virtual level column on the root elements, which will be incremented along the hierarchy.
The CONNECT BY clause has a dedicated ORDER BY clause (ORDER SIBLINGS BY) to sort the elements of a hierarchy.
SELECT LEVEL, LPAD(' ',2*(LEVEL-1)) || e.ename AS ename
, e.empno, e.mgr
FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
START WITH e.mgr IS NULL
ORDER SIBLINGS BY e.ename
/
Same result can be achieved using the recursive WITH clause with the SEARCH clause.
WITH r_emps (ename, empno, mgr, lvl)
AS (SELECT e.ename, e.empno, e.mgr, 1 AS lvl
FROM emp e
WHERE e.mgr IS NULL
UNION ALL
SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl
FROM emp e
INNER JOIN r_emps m ON (m.empno = e.mgr))
SEARCH DEPTH FIRST BY ename SET sorting
SELECT lvl, LPAD(' ',2*(lvl-1)) || e.ename AS ename
, e.empno ,e.mgr
FROM r_emps e
ORDER BY e.sorting
/
SEARCH DEPTH FIRST will show the children before the siblings whereas SEARCH BREADTH FIRST would show the siblings before the children, the ename column is used as the order attribute within the hierarchy and assigned to the attribute sorting which can afterwards be used in the outermost query.
05 Monday Oct 2009
Posted in ORACLE 11g Release 2, Uncategorized
The first example uses a recursive query which starts with the first node of every group and only shows those rows, where the whole string has been aggregated (connect_by_isleaf = 1).
SELECT deptno
, rn
, TRIM(leading ',' FROM SYS_CONNECT_BY_PATH(ename,','))
FROM (SELECT deptno
, ename
, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) rn
FROM emp)
WHERE connect_by_isleaf = 1
START WITH rn = 1
CONNECT BY PRIOR deptno = deptno
AND PRIOR rn + 1 = rn
/
another solution is to use xml functions like xmlagg (aggregate) and xmlelement.
SELECT deptno
,RTRIM(XMLAGG(XMLELEMENT(e,ename,',').EXTRACT('//text()')),',') name
FROM emp
GROUP BY deptno
/
one more solution even if not documented…
SELECT deptno, WM_CONCAT(ename)
FROM emp
GROUP by deptno
/
and if you need the enames to be ordered…
SELECT deptno
, WM_CONCAT(ename)
FROM (SELECT deptno, ename
FROM emp
ORDER BY ename)
GROUP by deptno
/
Another solution is to write your own group function
CREATE OR REPLACE TYPE ListAggregator IS OBJECT (
vList VARCHAR2(4000)
,STATIC FUNCTION ODCIAggregateInitialize (sctx IN OUT ListAggregator) RETURN NUMBER
,MEMBER FUNCTION ODCIAggregateIterate (SELF IN OUT ListAggregator
, value IN VARCHAR2 ) RETURN NUMBER
,MEMBER FUNCTION ODCIAggregateTerminate (self IN OUT ListAggregator
, returnValue OUT VARCHAR2
, flags IN NUMBER ) RETURN NUMBER
,MEMBER FUNCTION ODCIAggregateMerge (self IN OUT ListAggregator
, ctx2 IN ListAggregator ) RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY ListAggregator IS
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT ListAggregator) RETURN NUMBER IS
BEGIN
sctx := ListAggregator(NULL);
RETURN ODCIConst.SUCCESS;
END ODCIAggregateInitialize;
MEMBER FUNCTION ODCIAggregateIterate (self IN OUT ListAggregator
, value IN VARCHAR2 ) RETURN NUMBER IS
BEGIN
IF value IS NOT NULL
THEN
self.vList := concat(concat(self.vList,', '),value);
END IF;
RETURN ODCIConst.SUCCESS;
END ODCIAggregateIterate;
MEMBER FUNCTION ODCIAggregateTerminate (self IN OUT ListAggregator
, returnValue OUT VARCHAR2
, flags IN NUMBER ) RETURN NUMBER IS
BEGIN
returnValue := LTrim(self.vList, ', ');
RETURN ODCIConst.SUCCESS;
END ODCIAggregateTerminate;
MEMBER FUNCTION ODCIAggregateMerge (self IN OUT ListAggregator
, ctx2 IN ListAggregator) RETURN NUMBER IS
BEGIN
IF self.vList IS NULL
THEN
self.vList := ctx2.vList;
ELSIF ctx2.vList IS NOT NULL
THEN
self.vList := concat(concat(self.vList,', '),ctx2.vList);
END IF;
RETURN ODCIConst.SUCCESS;
END ODCIAggregateMerge;
END;
/
CREATE OR REPLACE FUNCTION LIST(vValue IN VARCHAR2) RETURN VARCHAR2
PARALLEL_ENABLE
AGGREGATE
USING ListAggregator;
/
SELECT deptno
, LIST(ename)
FROM emp
GROUP BY deptno
/
and finally if you are on ORACLE 11R2, there is a built-in function to do list aggregation
SELECT LISTAGG(ename,',') WITHIN GROUP (ORDER BY ename)
FROM emp
GROUP BY deptno
/