Wednesday 19 February 2014

Two Interactive Reports on one Page

Within APEX you normally can't define two Interactive Reports on the same page. But sometimes you do need that kind of solution. You can use Ajax to get the data into your form, but using an IFrame seems a more elegant way. There are some drawbacks using IFrame but "every advantage has it's disadvantage".

But this is how it works using an IFrame:

1. Create a page (ex. 24) with an Interactive Report on DEMO_ORDER_ITEMS - set the Page Template to 'Printer Friendly' to get rid of the regular header and footer stuff.
2. Create a page (ex. 23) with an Interactive Report on DEMO_ORDERS
3. On page 23 create an HTML Region with as Region Source:

  <IFRAME src="f?p=&APP_ID.:24:&SESSION."
  style="width:600px;height:600px"
  name="details"
  scrolling="no" marginwidth="0" marginheight="0"
  frameborder="0" vspace="0" hspace="0"  /> 
 
4. Set the Link Column attributes of the Interactive Report on DEMO_ORDERS to:
- Link Column : Custom Target
- Link Attributes : target="details" (that's the name of the IFRAME)
- Target Page : 24
- Clear Cache : 24,RIR (will clear the IR cache on Page 24)
- Item 1 : IR_ORDER_ID as the Name, #ORDER_ID# as the Value (will set ORDER_ID as Search Item.


Now if you click on the Column Link on the left IR, the right IR gets synchronized. Isn't that cool?
See the live example on apex.oracle.com.

Example:-

Steps to create multiple IR on same page:-

1) Create page 1 with an Interactive Report.
2) Create page 2 with another Interactive Report.
3) Create page 3.
  • Add 2 HTML Region , one for each IR.
  • Edit the first HTML Region. Go to Source Tab and add the below code

    <iframe src ="f?p=&APP_ID.:1:&SESSION." height=600 width=600 frameborder=0>
    </iframe>

  • Edit the second HTML Region.Go to Source Tab and add the below code.

    <iframe src ="f?p=&APP_ID.:2:&SESSION." height=600 width=600 frameborde
    </iframe>

Friday 7 February 2014

String Aggregation Techniques

On occasion it is necessary to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value. Using the SCOTT.EMP table as an example, we might want to retrieve a list of employees for each department. Below is a list of the base data and the type of output we would like to return from an aggregate query.

Base Data:

    DEPTNO ENAME
---------- ----------
        20 SMITH
        30 ALLEN
        30 WARD
        20 JONES
        30 MARTIN
        30 BLAKE
        10 CLARK
        20 SCOTT
        10 KING
        30 TURNER
        20 ADAMS
        30 JAMES
        20 FORD
        10 MILLER

Desired Output:

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
 
 

LISTAGG Analystic Function in 11g Release 2

 The LISTAGG analytic function was introduced in Oracle 11g Release 2, making it very easy to aggregate strings. The nice thing about this function is it also allows us to order the elements in the concatenated list. If you are using 11g Release 2 you should use this function for string aggregation.

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.
 

WM_CONCAT Built-in Function (Not Supported)

If you are not running 11g Release 2, but are running a version of the database where the WM_CONCAT function is present, then it is a zero effort solution as it performs the aggregation for you. It is actually an example of a user defined aggregate function described below, but Oracle have done all the work for you.

SELECT deptno, wm_concat(ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

3 rows selected.
 

User-Defined Aggregate Function

The WM_CONCAT function described above is an example of a user-defined aggregate function that Oracle have already created for you. If you don't want to use WM_CONCAT, you can create your own user-defined aggregate function as described at

CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),

  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
     RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER
);
/
SHOW ERRORS


CREATE OR REPLACE TYPE BODY t_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER IS
  BEGIN
    sctx := t_string_agg(NULL);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := self.g_string || ',' || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;
/
SHOW ERRORS


CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
SHOW ERRORS
 
The aggregate function is implemented using a type and type body, 
and is used within a query. 
 
COLUMN employees FORMAT A50

SELECT deptno, string_agg(ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
 

Specific Function

One approach is to write a specific function to solve the problems. The get_employees function listed below returns a list of employees for the specified department.

CREATE OR REPLACE FUNCTION get_employees (p_deptno  in  emp.deptno%TYPE)
  RETURN VARCHAR2
IS
  l_text  VARCHAR2(32767) := NULL;
BEGIN
  FOR cur_rec IN (SELECT ename FROM emp WHERE deptno = p_deptno) LOOP
    l_text := l_text || ',' || cur_rec.ename;
  END LOOP;
  RETURN LTRIM(l_text, ',');
END;
/
SHOW ERRORS
 
The function can then be incorporated into a query as follows.
COLUMN employees FORMAT A50

SELECT deptno,
       get_employees(deptno) AS employees
FROM   emp
GROUP by deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES 
 
To reduce the number of calls to the function, and thereby improve performance, 
we might want to filter the rows in advance. 
 
SELECT e.deptno,
       get_employees(e.deptno) AS employees
FROM   (SELECT DISTINCT deptno
        FROM   emp) e;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
        
3 rows selected.
 

Generic Function using Ref Cursor

 An alternative approach is to write a function to concatenate values passed using a ref cursor. This is essentially the same as the previous example, except that the cursor is passed in making it generic, as shown below.

CREATE OR REPLACE FUNCTION concatenate_list (p_cursor IN  SYS_REFCURSOR)
  RETURN  VARCHAR2
IS
  l_return  VARCHAR2(32767); 
  l_temp    VARCHAR2(32767);
BEGIN
  LOOP
    FETCH p_cursor
    INTO  l_temp;
    EXIT WHEN p_cursor%NOTFOUND;
    l_return := l_return || ',' || l_temp;
  END LOOP;
  RETURN LTRIM(l_return, ',');
END;
 
The CURSOR function is used to allow a query to be passed to the function 
as a ref cursor, as shown below. 
 
SELECT e1.deptno,
       concatenate_list(CURSOR(SELECT e2.ename FROM emp e2 WHERE e2.deptno = e1.deptno)) employees
FROM   emp e1
GROUP BY e1.deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

3 rows selected.
 
Once again, the total number of function calls can be reduced by 
filtering the distinct values, rather than calling the function for each
 row.
 
SELECT deptno,
       concatenate_list(CURSOR(SELECT e2.ename FROM emp e2 WHERE 
       e2.deptno = e1.deptno)) employees
FROM   (SELECT DISTINCT deptno
        FROM emp) e1;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
 

 

ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i

SELECT deptno,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
FROM   (SELECT deptno,
               ename,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
        FROM   emp)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD 
 

COLLECT function in Oracle 10g

COLUMN employees FORMAT A50

SELECT deptno,
       tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) AS employees
FROM   emp
GROUP BY deptno;
       
    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
        

 

 

 

 

 

 

RANK, DENSE_RANK, FIRST and LAST Analytic Functions

This article gives and overview of the RANK, DENSE_RANK, FIRST and LAST analytic functions.
Related articles.


SELECT empno,
       deptno,
       sal,
       RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM   emp;

     EMPNO     DEPTNO        SAL       rank
---------- ---------- ---------- ----------
      7934         10       1300          1
      7782         10       2450          2
      7839         10       5000          3
      7369         20        800          1
      7876         20       1100          2
      7566         20       2975          3
      7788         20       3000          4
      7902         20       3000          4
      7900         30        950          1
      7654         30       1250          2
      7521         30       1250          2
      7844         30       1500          4
      7499         30       1600          5
      7698         30       2850          6 
 

DENSE_RANK

SELECT empno,
       deptno,
       sal,
       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM   emp;

     EMPNO     DEPTNO        SAL       rank
---------- ---------- ---------- ----------
      7934         10       1300          1
      7782         10       2450          2
      7839         10       5000          3
      7369         20        800          1
      7876         20       1100          2
      7566         20       2975          3
      7788         20       3000          4
      7902         20       3000          4
      7900         30        950          1
      7654         30       1250          2
      7521         30       1250          2
      7844         30       1500          3
      7499         30       1600          4
      7698         30       2850          5 

FIRST and LAST

SELECT empno,
       deptno,
       sal,
       MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) "Lowest",
       MAX(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) "Highest"
FROM   emp
ORDER BY deptno, sal;

     EMPNO     DEPTNO        SAL     Lowest    Highest
---------- ---------- ---------- ---------- ----------
      7934         10       1300       1300       5000
      7782         10       2450       1300       5000
      7839         10       5000       1300       5000
      7369         20        800        800       3000
      7876         20       1100        800       3000
      7566         20       2975        800       3000
      7788         20       3000        800       3000
      7902         20       3000        800       3000
      7900         30        950        950       2850
      7654         30       1250        950       2850
      7521         30       1250        950       2850
      7844         30       1500        950       2850
      7499         30       1600        950       2850
      7698         30       2850        950       2850

 

 

FIRST_VALUE

The FIRST_VALUE analytic function is similar to the FIRST analytic function, allowing you to return the first result from an ordered set.

SELECT empno,
       deptno,
       sal,
       FIRST_VALUE(sal) IGNORE NULLS 
         OVER (PARTITION BY deptno ORDER BY sal) AS lowest_in_dept
FROM   emp;

     EMPNO     DEPTNO        SAL LOWEST_IN_DEPT
---------- ---------- ---------- --------------
      7934         10       1300           1300
      7782         10       2450           1300
      7839         10       5000           1300
      7369         20        800            800
      7876         20       1100            800
      7566         20       2975            800
      7788         20       3000            800
      7902         20       3000            800
      7900         30        950            950
      7654         30       1250            950
      7521         30       1250            950
      7844         30       1500            950
      7499         30       1600            950
      7698         30       2850            950 
 
The "{RESPECT | IGNORE} NULLS" clause indicates if NULLs are considered when determining results.
The windowing clause can be used to alter the window of operation. The following example uses "ROWS 1 PRECEDING" to give a result similar, but not quite the same, to a LAG of 1 row.

SELECT empno,
       deptno,
       sal,
       FIRST_VALUE(sal) IGNORE NULLS
         OVER (PARTITION BY deptno ORDER BY sal ROWS 1 PRECEDING) AS preceding_in_dept
FROM   emp;

     EMPNO     DEPTNO        SAL PRECEDING_IN_DEPT
---------- ---------- ---------- -----------------
      7934         10       1300              1300
      7782         10       2450              1300
      7839         10       5000              2450
      7369         20        800               800
      7876         20       1100               800
      7566         20       2975              1100
      7788         20       3000              2975
      7902         20       3000              3000
      7900         30        950               950
      7654         30       1250               950
      7521         30       1250              1250
      7844         30       1500              1250
      7499         30       1600              1500
      7698         30       2850              1600 

LAST_VALUE

The LAST_VALUE analytic function is similar to the LAST analytic function, allowing you to return the last result from an ordered set. Using the default windowing clause the result can be a little unexpected.

SELECT empno,
       deptno,
       sal,
       LAST_VALUE(sal) IGNORE NULLS
         OVER (PARTITION BY deptno ORDER BY sal) AS highest_in_dept
FROM   emp;

     EMPNO     DEPTNO        SAL HIGHEST_IN_DEPT
---------- ---------- ---------- ---------------
      7934         10       1300            1300
      7782         10       2450            2450
      7839         10       5000            5000
      7369         20        800             800
      7876         20       1100            1100
      7566         20       2975            2975
      7788         20       3000            3000
      7902         20       3000            3000
      7900         30        950             950
      7654         30       1250            1250
      7521         30       1250            1250
      7844         30       1500            1500
      7499         30       1600            1600
      7698         30       2850            2850
 
This is because the default windowing clause is
 "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW", 
which in this example means the current row will always be the last value. 
Altering the windowing clause to 
"RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
gives us the result we probably expected. 

SELECT empno,
       deptno,
       sal,
       LAST_VALUE(sal) IGNORE NULLS
         OVER (PARTITION BY deptno ORDER BY sal RANGE BETWEEN
           UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS highest_in_dept
FROM   emp;

     EMPNO     DEPTNO        SAL HIGHEST_IN_DEPT
---------- ---------- ---------- ---------------
      7934         10       1300            5000
      7782         10       2450            5000
      7839         10       5000            5000
      7369         20        800            3000
      7876         20       1100            3000
      7566         20       2975            3000
      7788         20       3000            3000
      7902         20       3000            3000
      7900         30        950            2850
      7654         30       1250            2850
      7521         30       1250            2850
      7844         30       1500            2850
      7499         30       1600            2850
      7698         30       2850            2850

 

 

 

 

 

 

CUME_DIST in oracle

The following example calculates the salary percentile for each employee in the purchasing division. For example, 40% of clerks have salaries less than or equal to Himuro.
SELECT job_id, last_name, salary, CUME_DIST() 
   OVER (PARTITION BY job_id ORDER BY salary) AS cume_dist
   FROM employees
   WHERE job_id LIKE 'PU%';

JOB_ID     LAST_NAME                     SALARY  CUME_DIST
---------- ------------------------- ---------- ----------
PU_CLERK   Colmenares                      2500         .2
PU_CLERK   Himuro                          2600         .4
PU_CLERK   Tobias                          2800         .6
PU_CLERK   Baida                           2900         .8
PU_CLERK   Khoo                            3100          1
PU_MAN     Raphaely                       11000          1