Category Archives: Uncategorized

Recursive WITH clause (1)

19 Monday Sep 2011

Posted by in ORACLE 11g Release 2, Uncategorized

≈ Leave a comment

Tags

, , ,

how to implement CONNECT BY features

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.

Basic recursive query

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.

Formatting the result set

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.

Sorting the result set

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.

5 ways to aggregate columns into a comma separated string

05 Monday Oct 2009

Posted by in ORACLE 11g Release 2, Uncategorized

≈ Leave a comment

Tags

, , , , ,

Connect By

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
 /

XMLAGG

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
 /

 WM_CONCAT

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
 /

 User defined aggregator

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
/

LISTAGG

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
 /

Finding Gaps

15 Saturday Aug 2009

Posted by in Tipps and Tricks, Uncategorized

≈ Leave a comment

Tags

,

One of the things often asked for is how to find gaps in a list of numbers. So here’s a rather old suggestion:

WITH gaps (id) AS (SELECT  1 FROM dual UNION ALL
                   SELECT  3 FROM dual UNION ALL
                   SELECT  4 FROM dual UNION ALL
                   SELECT  7 FROM dual UNION ALL
                   SELECT 15 FROM dual)
    ,borders AS (SELECT MIN(id) mins
                       ,MAX(id) maxs
                   FROM gaps)
SELECT mins + (ROWNUM - 1) AS missing_value
  FROM borders
CONNECT BY mins + (ROWNUM - 1) <> maxs
MINUS
SELECT id
  FROM gaps
/

Calendar Query

14 Tuesday Jul 2009

Posted by in Uncategorized

≈ Leave a comment

Tags

,

This post shows how to create a calendar view using pure SQL.

E.g. September 2016 should look like this:

WEEK MON TUE WED THU FRI SAT SUN
---- --- --- --- --- --- --- ---
  35              01  02  03  04
  36  05  06  07  08  09  10  11
  37  12  13  14  15  16  17  18
  38  19  20  21  22  23  24  25
  39  26  27  28  29  30

To create this kind of calendar you can use recursive queries (aka connect by queries), to create a list of all days belonging to the current month.

SELECT TRUNC(SYSDATE,'Month') + (ROWNUM - 1) AS theDate
  FROM dual
CONNECT BY TRUNC(SYSDATE,'Month') + (ROWNUM - 1) <= LAST_DAY(SYSDATE);

Having this list of all days the only additional thing we have to consider is how to group and format this data. I have choosen a DECODE approach to group the dates to the appropriate weekday column.

WITH days_of_curr_month AS (SELECT TRUNC(SYSDATE,'Month') + (ROWNUM - 1) AS theDate
                               FROM dual
                            CONNECT BY TRUNC(SYSDATE,'Month') + (ROWNUM - 1) <= LAST_DAY(SYSDATE))   
    ,data_formatter     AS (SELECT TO_CHAR(theDate,'IYYY')    AS Iyear                                   
                                  ,TO_CHAR(theDate,'IW')      AS Iweek                                   
                                  ,TO_CHAR(theDate,'DY','NLS_DATE_LANGUAGE=AMERICAN') AS MyDateDay                                   
                                  ,TO_CHAR(theDate,'DD')      AS MyDate                               
                               FROM days_of_curr_month) 
SELECT IWeek                               AS Week       
      ,MAX(DECODE(MyDateDay,'MON',MyDate)) AS MON       
      ,MAX(DECODE(MyDateDay,'TUE',MyDate)) AS TUE       
      ,MAX(DECODE(MyDateDay,'WED',MyDate)) AS WED       
      ,MAX(DECODE(MyDateDay,'THU',MyDate)) AS THU       
      ,MAX(DECODE(MyDateDay,'FRI',MyDate)) AS FRI       
      ,MAX(DECODE(MyDateDay,'SAT',MyDate)) AS SAT       
      ,MAX(DECODE(MyDateDay,'SUN',MyDate)) AS SUN 
  FROM data_formatter 
 GROUP BY IYear ,Iweek
 ORDER BY IYear ,IWeek;

The IYear information is needed as in the ISO-Calendar dates like January 1 – January 3 or December 29 – December 31 may belong to the previous or the following calendar year (week 53 or 1) and we have to make sure, that week 1 of the following year is placed at the end of the list and week 53 of the previous year is placed on top.