Multidimensional data analysis with SQL

Using advanced SQL features, you can even perform multidimensional data analysis as well, enabling you to answer complex analytical questions.
This can be best understood by an example on group operations, aggregating data over multiple rows stored in several tables. Suppose you collect information about sales, storing the data in related tables within a single database schema. At the end of each quarter, you need to know the summarized sales figures for every region, for each month within that quarter.
To start with, let's create a new database schema, and the tables within it that are needed for this example. You can perform all these tasks from SQL*Plus.

CREATE USER usr IDENTIFIED BY usr;
GRANT connect, resource TO usr;
CONN usr/usr

CREATE TABLE salespersons (
emp_id VARCHAR2(10) PRIMARY KEY,
emp_name VARCHAR2(40)
)

CREATE TABLE regions (
reg_id VARCHAR2(2) PRIMARY KEY,
reg_name VARCHAR2(20)
)

CREATE TABLE orders(
ordno NUMBER PRIMARY KEY,
empid VARCHAR2(10) REFERENCES salespersons(emp_id),
regid VARCHAR2(2) REFERENCES regions(reg_id),
orddate DATE,
total NUMBER(10,2)
)

Next, you have to populate these tables with some data, so that you can perform queries against them. As the orders table refers to the salespersons and regions tables, you first have to populate these two tables with data. You might use the following statements for this:

INSERT INTO salespersons VALUES ('Abuzar', 'Abuzar Bukhari');
INSERT INTO salespersons VALUES ('Usman', 'Usman Bukhari');
INSERT INTO regions VALUES ('PK', 'Pakistan');
INSERT INTO regions VALUES ('SA', 'Saudi Arabia');

Now that you have filled up the salespersons and regions tables, you can move on and populate the orders table:

INSERT INTO orders VALUES
(1001, 'Abuzar', 'PK', '10-JAN-2010', 1450.00);
INSERT INTO orders VALUES
(1002, 'Abuzar', 'PK', '15-JAN-2010', 2310.00);
INSERT INTO orders VALUES
(1003, 'Usman', 'SA', '20-JAN-2010', 1480.00);
INSERT INTO orders VALUES
(1004, 'Abuzar', 'PK', '19-FEB-2010', 3700.00);
INSERT INTO orders VALUES
(1005, 'Usman', 'SA', '24-FEB-2010', 1850.00);
INSERT INTO orders VALUES
(1006, 'Usman', 'SA', '04-MAR-2010', 1770.00);
INSERT INTO orders VALUES
(1007, 'Usman', 'SA', '05-MAR-2010', 1210.00);
INSERT INTO orders VALUES
(1008, 'Abuzar', 'PK', '05-MAR-2010', 10420.00);
Commit;

You now have all the parts required to proceed with the example, and can issue a query that summarizes sales figures for every region for each month. Here is what this query might look like:

SELECT r.reg_name region, TO_CHAR(TO_DATE(EXTRACT(MONTH FROM (o.orddate)),'MM'),'Month') month, SUM(o.total) sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY ROLLUP(EXTRACT(MONTH FROM (o.orddate)), r.reg_name);

In the above query, you summarize the data representing the sales figures, generating subtotals for every region for each month, and then totals for each month with the help of the GROUP BY ... ROLLUP clause. The query results should look like this:

REGION               MONTH          SALES
-------------------- --------- ----------
Pakistan             January         3760
Saudi Arabia         January         1480
                     January         5240
Pakistan             February        3700
Saudi Arabia         February        1850
                     February        5550
Pakistan             March          10420
Saudi Arabia         March           2980
                     March          13400
                                    24190
As you can see, the query output contains not only monthly sales per region and month totals for each month, but also the grand total, which is nothing but the sum of the month totals. You might exclude this grand total from the report if you are using a partial ROLLUP as shown next:

SELECT r.reg_name region, TO_CHAR(TO_DATE(EXTRACT(MONTH FROM (o.orddate)),'MM'),'Month') month, SUM(o.total) sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY EXTRACT(MONTH FROM (o.orddate)), ROLLUP(r.reg_name);

As the month field is excluded from the ROLLUP here, the month totals will not be summarized:

REGION               MONTH          SALES
-------------------- --------- ----------
Pakistan             January         3760
Saudi Arabia         January         1480
                     January         5240
Pakistan             February        3700
Saudi Arabia         February        1850
                     February        5550
Pakistan             March          10420
Saudi Arabia         March           2980
                     March          13400
                    
Changing the order in which the parameters of ROLLUP appear will also affect the report. In our example, if you move the r. reg_name parameter to the first position, the report will generate totals for each region rather than for each month. Here is the query:

SELECT r.reg_name region, TO_CHAR(TO_DATE(EXTRACT(MONTH FROM (o.orddate)),'MM'),'Month') month, SUM(o.total) sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY ROLLUP(r.reg_name, EXTRACT(MONTH FROM (o.orddate)));   

Here is its output:
REGION               MONTH          SALES
-------------------- --------- ----------
Pakistan             January         3760
Pakistan             February        3700
Pakistan             March          10420
Pakistan                            17880
Saudi Arabia         January         1480
Saudi Arabia         February        1850
Saudi Arabia         March           2980
Saudi Arabia                         6310
                                    24190
As you can see, in the above report there are no longer any month totals. Instead, it provides you with the totals for each region.

Cubing

So by now you are probably wondering if it's possible to have the totals for both each month and each region, thus having all possible combinations of the grouping columns within a single report. While there is more than one way to achieve this, using the CUBE extension of the GROUP BY clause is the most elegant and effective one.

The following figure gives a graphical depiction of how CUBE differs from ROLLUP when it comes to generating summary information:

Switching to SQL, you might write the following query using the CUBE function in the GROUP BY clause:

SELECT r.reg_name region, TO_CHAR(TO_DATE(EXTRACT(MONTH FROM (o.orddate)),'MM'),'Month') month, SUM(o.total) sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY CUBE(EXTRACT(MONTH FROM (o.orddate)), r.reg_name);                                                    

The previous query should generate the following report:
REGION               MONTH          SALES
-------------------- --------- ----------
                                    24190
Pakistan                            17880
Saudi Arabia                         6310
                     January         5240
Pakistan             January         3760
Saudi Arabia         January         1480
                     February        5550
Pakistan             February        3700
Saudi Arabia         February        1850
                     March          13400
Pakistan             March          10420
Saudi Arabia         March           2980


As you can see, you now have all possible subtotals and totals combinations presented in the report. This is why changing the order of columns in CUBE won't affect the report, unlike with ROLLUP. SO, the following query should actually give you the same results as above:

SELECT r.reg_name region, TO_CHAR(TO_DATE(EXTRACT(MONTH FROM (o.orddate)),'MM'),'Month') month, SUM(o.total) sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY CUBE(r.reg_name, EXTRACT(MONTH FROM (o.orddate)));

However, the order of rows in the report will be a little different:
REGION               MONTH          SALES
-------------------- --------- ----------
                                    24190
                     January         5240
                     February        5550
                     March          13400
Pakistan                            17880
Pakistan             January         3760
Pakistan             February        3700
Pakistan             March          10420
Saudi Arabia                         6310
Saudi Arabia         January         1480
Saudi Arabia         February        1850
Saudi Arabia         March           2980

Just as there is a partial ROLLUP, there is also a partial CUBE. For example, you might issue the following query:

SELECT r.reg_name region, TO_CHAR(TO_DATE(EXTRACT(MONTH FROM (o.orddate)),'MM'),'Month') month, SUM(o.total) sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY r.reg_name, CUBE(EXTRACT(MONTH FROM (o.orddate)));

This should generate the following output:
REGION               MONTH          SALES
-------------------- --------- ----------
Pakistan                            17880
Pakistan             January         3760
Pakistan             February        3700
Pakistan             March          10420
Saudi Arabia                         6310
Saudi Arabia         January         1480
Saudi Arabia         February        1850
Saudi Arabia         March           2980

As you can see, it only includes the totals for each region, and not for each month.Interestingly, you would get the same results if you replaced CUBE with ROLLUP, issuing the following query:

SELECT r.reg_name region, TO_CHAR(TO_DATE(EXTRACT(MONTH FROM (o.orddate)),'MM'),'Month') month, SUM(o.total) sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY r.reg_name, ROLLUP(EXTRACT(MONTH FROM (o.orddate)));

Thus, a one column CUBE operation produces the same results as the identical ROLLUP operation.

Generating reports with only summary rows

In some cases, you may not need to include the rows that represent the subtotals generated by GROUP BY, but include only the total rows. This is where the GROUPING SETS extension of the GROUP BY clause may come in handy:

SELECT r.reg_name region, TO_CHAR(TO_DATE(EXTRACT(MONTH FROM (o.orddate)),'MM'),'Month') month, SUM(o.total) sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY GROUPING SETS(r.reg_name, EXTRACT(MONTH FROM (o.orddate)));

This query should produce the following report:
REGION               MONTH          SALES
-------------------- --------- ----------
Pakistan                            17880
Saudi Arabia                         6310
                     January         5240
                     February        5550
                     March          13400
                    
You receive an interesting result when performing a partial GROUPING SET operation like the one shown next:

SELECT r.reg_name region, TO_CHAR(TO_DATE(EXTRACT(MONTH FROM (o.orddate)),'MM'),'Month') month, SUM(o.total) sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY r.reg_name, GROUPING SETS(EXTRACT(MONTH FROM (o.orddate)));                    

This should give you the following results:

REGION               MONTH          SALES
-------------------- --------- ----------
Pakistan             March          10420
Pakistan             January         3760
Saudi Arabia         March           2980
Saudi Arabia         January         1480
Saudi Arabia         February        1850
Pakistan             February        3700

It's interesting to note that the above report doesn't include summary rows—neither for each month nor for each region. Instead, it shows you all those subtotal rows generated by GROUP BY and excluded from the full GROUPING SET operation discussed earlier in this section. In this sense, a partial GROUPING SET operation is a reverse operation to the corresponding full one.

Ranking
You might, for example, compose a query that will compute a rank for each region, based on the sales figures. To better understand how ranking works, though, let's first look at the query that simply summarizes sales figures per region:

SELECT r.reg_name region, SUM(o.total) sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY GROUPING SETS(r.reg_name);

Here is the output you should see:
REGION                    SALES
-------------------- ----------
Pakistan                  17880
Saudi Arabia               6310

Now let's look at the query that will compute a sales rank for each region in ascending order:

SELECT r.reg_name region, SUM(o.total) sales, RANK() OVER (ORDER BY SUM(o.total) ASC) rank
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY GROUPING SETS(r.reg_name);

This time, the query results should look like this:
REGION                    SALES       RANK
-------------------- ---------- ----------
Saudi Arabia               6310          1
Pakistan                  17880          2


As you can see in the above rows, not only are region sales arranged in ascending order, but the rank column has also appeared. If you don't want to see the rank column, you might remove it from the select list and add the ORDER BY clause with the rank function, rewriting the query as follows:

SELECT r.reg_name region, SUM(o.total) sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY GROUPING SETS(r.reg_name)
ORDER BY RANK() OVER (ORDER BY SUM(o.total) ASC);

This should produce the following output:
REGION                    SALES
-------------------- ----------
Saudi Arabia               6310
Pakistan                  17880

As you can see, a little trick of moving the rank function from the select list to the ORDER BY clause lets you exclude the ranking column from the output, keeping sales figures in the sales column arranged in ascending order.

Windowing
Windowing functions make up another important group of analytic SQL functions. The idea behind windowing functions is that they enable aggregate calculations to be made within a "sliding window" that may float down as you proceed through the result set. For example, you might want to include a sliding average column in the report containing total monthly sales. This column would contain the average sales for each row, based on the sales figures in the preceding, current, and following rows. Thus, the average for the January row will be calculated based on the value of the sales field in the January row, as well as the value of the sales field in the February row. In turn, the average for the February row will be determined using the sales field's values throughout all three rows. Finally, the average for the March row will be computed based on the sales for February and March.

The following diagram gives a graphical illustration of this example: 

Let's first look at the query that simply calculates total monthly sales, which should look like this:

SELECT TO_CHAR(TO_DATE(EXTRACT(MONTH FROM (o.orddate)),'MM'),'Month')

month, SUM(o.total) sales

FROM orders o

GROUP BY TO_CHAR(TO_DATE(EXTRACT(MONTH FROM (o.orddate)),'MM'),'Month');

Here is the output:

MONTH          SALES

--------- ----------

January         5240

February        5550

March          13400

Now let's modify the query and define a "window" that will float down the above rows, calculating a value for the sliding_avg field. In the following query, note the use of the ROWS BETWEEN clause, and the keywords 1 PRECEDING and 1 FOLLOWING defining the parameters of the sliding window:

SELECT TO_CHAR(TO_DATE(EXTRACT(MONTH FROM (o.orddate)),'MM'),'Month')

month, SUM(o.total) sales,

AVG(SUM(o.total)) OVER (ORDER BY TO_CHAR(TO_DATE(EXTRACT(MONTH FROM

(o.orddate)),'MM'),'Month') ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

sliding_avg

FROM orders o

GROUP BY TO_CHAR(TO_DATE(EXTRACT(MONTH FROM (o.orddate)),'MM'),'Month' );

The output should look like the following:

MONTH          SALES SLIDING_AVG

--------- ---------- -----------

February        5550        5395

January         5240  8063.33333

March          13400        9320

Aside from the AVG function, you can apply the windowing technique using the other aggregate functions, such as SUM, MIN, and MAX.

Analytic functions by Example

From orafaq
------------------
This article provides a clear, thorough concept of analytic functions and its various options by a series of simple yet concept building examples. The article is intended for SQL coders, who for might be not be using analytic functions due to unfamiliarity with its cryptic syntax or uncertainty about its logic of operation. Often I see that people tend to reinvent the feature provided by analytic functions by native join and sub-query SQL. This article assumes familiarity with basic Oracle SQL, sub-query, join and group function from the reader. Based on that familiarity, it builds the concept of analytic functions through a series of examples.
It is true that whatever an analytic function does can be done by native SQL, with join and sub-queries. But the same routine done by analytic function is always faster, or at least as fast, when compared to native SQL. Moreover, I am not considering here the amount of time that is spent in coding the native SQLs, testing, debugging and tuning them.
The general syntax of analytic function is:
Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <....>] [<window_clause>] )
<window_clause> is like "ROW <?>" or "RANK <?>"
All the keywords will be dealt in details as we walk through the examples. The script for creating the schema (SCOTT) on which the example queries of this article are run can be obtained in ORACLE_HOME/sqlplus/demo/demobld.sql of any standard Oracle installation.

How are analytic functions different from group or aggregate functions?

SELECT deptno,
COUNT(*) DEPT_COUNT
FROM emp
WHERE deptno IN (20, 30)
GROUP BY deptno;

DEPTNO                 DEPT_COUNT             
---------------------- ---------------------- 
20                     5                      
30                     6                      

2 rows selected

Query-1

Consider the Query-1 and its result. Query-1 returns departments and their employee count. Most importantly it groups the records into departments in accordance with the GROUP BY clause. As such any non-"group by" column is not allowed in the select clause.

SELECT empno, deptno, 
COUNT(*) OVER (PARTITION BY 
deptno) DEPT_COUNT
FROM emp
WHERE deptno IN (20, 30);

     EMPNO     DEPTNO DEPT_COUNT
---------- ---------- ----------
      7369         20          5
      7566         20          5
      7788         20          5
      7902         20          5
      7876         20          5
      7499         30          6
      7900         30          6
      7844         30          6
      7698         30          6
      7654         30          6
      7521         30          6

11 rows selected.

Query-2

Now consider the analytic function query (Query-2) and its result. Note the repeating values of DEPT_COUNT column.
This brings out the main difference between aggregate and analytic functions. Though analytic functions give aggregate result they do not group the result set. They return the group value multiple times with each record. As such any other non-"group by" column or expression can be present in the select clause, for example, the column EMPNO in Query-2.
Analytic functions are computed after all joins, WHERE clause, GROUP BY and HAVING are computed on the query. The main ORDER BY clause of the query operates after the analytic functions. So analytic functions can only appear in the select list and in the main ORDER BY clause of the query.
In absence of any PARTITION or <window_clause> inside the OVER( ) portion, the function acts on entire record set returned by the where clause. Note the results of Query-3 and compare it with the result of aggregate function query Query-4.

SELECT empno, deptno, 
COUNT(*) OVER ( ) CNT
FROM emp
WHERE deptno IN (10, 20)
ORDER BY 2, 1;

     EMPNO     DEPTNO        CNT
---------- ---------- ----------
      7782         10          8
      7839         10          8
      7934         10          8
      7369         20          8
      7566         20          8
      7788         20          8
      7876         20          8
      7902         20          8

Query-3

SELECT COUNT(*) FROM emp
WHERE deptno IN (10, 20);

  COUNT(*)
----------
         8

Query-4

How to break the result set in groups or partitions?

It might be obvious from the previous example that the clause PARTITION BY is used to break the result set into groups. PARTITION BY can take any non-analytic SQL expression.
Some functions support the <window_clause> inside the partition to further limit the records they act on. In the absence of any <window_clause> analytic functions are computed on all the records of the partition clause.
The functions SUM, COUNT, AVG, MIN, MAX are the common analytic functions the result of which does not depend on the order of the records.
Functions like LEAD, LAG, RANK, DENSE_RANK, ROW_NUMBER, FIRST, FIRST VALUE, LAST, LAST VALUE depends on order of records. In the next example we will see how to specify that.

How to specify the order of the records in the partition?

The answer is simple, by the "ORDER BY" clause inside the OVER( ) clause. This is different from the ORDER BY clause of the main query which comes after WHERE. In this section we go ahead and introduce each of the very useful functions LEAD, LAG, RANK, DENSE_RANK, ROW_NUMBER, FIRST, FIRST VALUE, LAST, LAST VALUE and show how each depend on the order of the record.
The general syntax of specifying the ORDER BY clause in analytic function is:
ORDER BY <sql_expr> [ASC or DESC] NULLS [FIRST or LAST]
The syntax is self-explanatory.

ROW_NUMBER, RANK and DENSE_RANK

All the above three functions assign integer values to the rows depending on their order. That is the reason of clubbing them together.
ROW_NUMBER( ) gives a running serial number to a partition of records. It is very useful in reporting, especially in places where different partitions have their own serial numbers. In Query-5, the function ROW_NUMBER( ) is used to give separate sets of running serial to employees of departments 10 and 20 based on their HIREDATE.

SELECT empno, deptno, hiredate,
ROW_NUMBER( ) OVER (PARTITION BY
deptno ORDER BY hiredate
NULLS LAST) SRLNO
FROM emp
WHERE deptno IN (10, 20)
ORDER BY deptno, SRLNO;

EMPNO  DEPTNO HIREDATE       SRLNO
------ ------- --------- ----------
  7782      10 09-JUN-81          1
  7839      10 17-NOV-81          2
  7934      10 23-JAN-82          3
  7369      20 17-DEC-80          1
  7566      20 02-APR-81          2
  7902      20 03-DEC-81          3
  7788      20 09-DEC-82          4
  7876      20 12-JAN-83          5

8 rows selected.

Query-5 (ROW_NUMBER example)

RANK and DENSE_RANK both provide rank to the records based on some column value or expression. In case of a tie of 2 records at position N, RANK declares 2 positions N and skips position N+1 and gives position N+2 to the next record. While DENSE_RANK declares 2 positions N but does not skip position N+1.
Query-6 shows the usage of both RANK and DENSE_RANK. For DEPTNO 20 there are two contenders for the first position (EMPNO 7788 and 7902). Both RANK and DENSE_RANK declares them as joint toppers. RANK skips the next value that is 2 and next employee EMPNO 7566 is given the position 3. For DENSE_RANK there are no such gaps.

SELECT empno, deptno, sal,
RANK() OVER (PARTITION BY deptno
ORDER BY sal DESC NULLS LAST) RANK,
DENSE_RANK() OVER (PARTITION BY
deptno ORDER BY sal DESC NULLS
LAST) DENSE_RANK
FROM emp
WHERE deptno IN (10, 20)
ORDER BY 2, RANK;

EMPNO  DEPTNO   SAL  RANK DENSE_RANK
------ ------- ----- ----- ----------
  7839      10  5000     1          1
  7782      10  2450     2          2
  7934      10  1300     3          3
  7788      20  3000     1          1
  7902      20  3000     1          1
  7566      20  2975     3          2
  7876      20  1100     4          3
  7369      20   800     5          4

8 rows selected.

Query-6 (RANK and DENSE_RANK example)

LEAD and LAG

LEAD has the ability to compute an expression on the next rows (rows which are going to come after the current row) and return the value to the current row. The general syntax of LEAD is shown below:
LEAD (<sql_expr>, <offset>, <default>) OVER (<analytic_clause>)
<sql_expr> is the expression to compute from the leading row.
<offset> is the index of the leading row relative to the current row.
<offset> is a positive integer with default 1.
<default> is the value to return if the <offset> points to a row outside the partition range.
The syntax of LAG is similar except that the offset for LAG goes into the previous rows.
Query-7 and its result show simple usage of LAG and LEAD function.

SELECT deptno, empno, sal,
LEAD(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) NEXT_LOWER_SAL,
LAG(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) PREV_HIGHER_SAL
FROM emp
WHERE deptno IN (10, 20)
ORDER BY deptno, sal DESC;

 DEPTNO  EMPNO   SAL NEXT_LOWER_SAL PREV_HIGHER_SAL
------- ------ ----- -------------- ---------------
     10   7839  5000           2450               0
     10   7782  2450           1300            5000
     10   7934  1300              0            2450
     20   7788  3000           3000               0
     20   7902  3000           2975            3000
     20   7566  2975           1100            3000
     20   7876  1100            800            2975
     20   7369   800              0            1100

8 rows selected.

Query-7 (LEAD and LAG)

FIRST VALUE and LAST VALUE function

The general syntax is:
FIRST_VALUE(<sql_expr>) OVER (<analytic_clause>)
The FIRST_VALUE analytic function picks the first record from the partition after doing the ORDER BY. The <sql_expr> is computed on the columns of this first record and results are returned. The LAST_VALUE function is used in similar context except that it acts on the last record of the partition.

-- How many days after the first hire of each department were the next
-- employees hired?

SELECT empno, deptno, hiredate ? FIRST_VALUE(hiredate)
OVER (PARTITION BY deptno ORDER BY hiredate) DAY_GAP
FROM emp
WHERE deptno IN (20, 30)
ORDER BY deptno, DAY_GAP;

     EMPNO     DEPTNO    DAY_GAP
---------- ---------- ----------
      7369         20          0
      7566         20        106
      7902         20        351
      7788         20        722
      7876         20        756
      7499         30          0
      7521         30          2
      7698         30         70
      7844         30        200
      7654         30        220
      7900         30        286

11 rows selected.

Query-8 (FIRST_VALUE)

FIRST and LAST function

The FIRST function (or more properly KEEP FIRST function) is used in a very special situation. Suppose we rank a group of record and found several records in the first rank. Now we want to apply an aggregate function on the records of the first rank. KEEP FIRST enables that.
The general syntax is:
Function( ) KEEP (DENSE_RANK FIRST ORDER BY <expr>) OVER (<partitioning_clause>)
Please note that FIRST and LAST are the only functions that deviate from the general syntax of analytic functions. They do not have the ORDER BY inside the OVER clause. Neither do they support any <window> clause. The ranking done in FIRST and LAST is always DENSE_RANK. The query below shows the usage of FIRST function. The LAST function is used in similar context to perform computations on last ranked records.

-- How each employee's salary compare with the average salary of the first
-- year hires of their department?

SELECT empno, deptno, TO_CHAR(hiredate,'YYYY') HIRE_YR, sal,
TRUNC(
AVG(sal) KEEP (DENSE_RANK FIRST
ORDER BY TO_CHAR(hiredate,'YYYY') )
OVER (PARTITION BY deptno)
     ) AVG_SAL_YR1_HIRE
FROM emp
WHERE deptno IN (20, 10)
ORDER BY deptno, empno, HIRE_YR;

     EMPNO     DEPTNO HIRE        SAL AVG_SAL_YR1_HIRE
---------- ---------- ---- ---------- ----------------
      7782         10 1981       2450             3725
      7839         10 1981       5000             3725
      7934         10 1982       1300             3725
      7369         20 1980        800              800
      7566         20 1981       2975              800
      7788         20 1982       3000              800
      7876         20 1983       1100              800
      7902         20 1981       3000              800

8 rows selected.

Query-9 (KEEP FIRST)

How to specify the Window clause (ROW type or RANGE type windows)?

Some analytic functions (AVG, COUNT, FIRST_VALUE, LAST_VALUE, MAX, MIN and SUM among the ones we discussed) can take a window clause to further sub-partition the result and apply the analytic function. An important feature of the windowing clause is that it is dynamic in nature.
The general syntax of the <window_clause> is

[ROW or RANGE] BETWEEN <start_expr> AND <end_expr>
<start_expr> can be any one of the following

1. UNBOUNDED PECEDING
   
2. CURRENT ROW
   
3. <sql_expr> PRECEDING or FOLLOWING.

<end_expr> can be any one of the following
1. UNBOUNDED FOLLOWING or
2. CURRENT ROW or
3. <sql_expr> PRECEDING or FOLLOWING.

For ROW type windows the definition is in terms of row numbers before or after the current row. So for ROW type windows <sql_expr> must evaluate to a positive integer.
For RANGE type windows the definition is in terms of values before or after the current ORDER. We will take this up in details latter.
The ROW or RANGE window cannot appear together in one OVER clause. The window clause is defined in terms of the current row. But may or may not include the current row. The start point of the window and the end point of the window can finish before the current row or after the current row. Only start point cannot come after the end point of the window. In case any point of the window is undefined the default is UNBOUNDED PRECEDING for <start_expr> and UNBOUNDED FOLLOWING for <end_expr>.
If the end point is the current row, syntax only in terms of the start point can be can be
[ROW or RANGE] [<start_expr> PRECEDING or UNBOUNDED PRECEDING ]
[ROW or RANGE] CURRENT ROW is also allowed but this is redundant. In this case the function behaves as a single-row function and acts only on the current row.

ROW Type Windows

For analytic functions with ROW type windows, the general syntax is:
Function( ) OVER (PARTITIN BY <expr1> ORDER BY <expr2,..> ROWS BETWEEN <start_expr> AND <end_expr>)
or
Function( ) OVER (PARTITON BY <expr1> ORDER BY <expr2,..> ROWS [<start_expr> PRECEDING or UNBOUNDED PRECEDING]
For ROW type windows the windowing clause is in terms of record numbers.
The query Query-10 has no apparent real life description (except column FROM_PU_C) but the various windowing clause are illustrated by a COUNT(*) function. The count simply shows the number of rows inside the window definition. Note the build up of the count for each column for the YEAR 1981.
The column FROM_P3_TO_F1 shows an example where start point of the window is before the current row and end point of the window is after current row. This is a 5 row window; it shows values less than 5 during the beginning and end.

-- The query below has no apparent real life description (except 
-- column FROM_PU_C) but is remarkable in illustrating the various windowing
-- clause by a COUNT(*) function.
 
SELECT empno, deptno, TO_CHAR(hiredate, 'YYYY') YEAR,
COUNT(*) OVER (PARTITION BY TO_CHAR(hiredate, 'YYYY')
ORDER BY hiredate ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) FROM_P3_TO_F1,
COUNT(*) OVER (PARTITION BY TO_CHAR(hiredate, 'YYYY')
ORDER BY hiredate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM_PU_TO_C,
COUNT(*) OVER (PARTITION BY TO_CHAR(hiredate, 'YYYY')
ORDER BY hiredate ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) FROM_P2_TO_P1,
COUNT(*) OVER (PARTITION BY TO_CHAR(hiredate, 'YYYY')
ORDER BY hiredate ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) FROM_F1_TO_F3
FROM emp
ORDEDR BY hiredate

 EMPNO  DEPTNO YEAR FROM_P3_TO_F1 FROM_PU_TO_C FROM_P2_TO_P1 FROM_F1_TO_F3
------ ------- ---- ------------- ------------ ------------- -------------
  7369      20 1980             1            1             0             0
  <font bgcolor=yellow>7499      30 1981             2            1             0             3
  7521      30 1981             3            2             1             3
  7566      20 1981             4            3             2             3
  7698      30 1981             5            4             3             3
  7782      10 1981             5            5             3             3
  7844      30 1981             5            6             3             3
  7654      30 1981             5            7             3             3
  7839      10 1981             5            8             3             2
  7900      30 1981             5            9             3             1
  7902      20 1981             4           10             3             0</font>
  7934      10 1982             2            1             0             1
  7788      20 1982             2            2             1             0
  7876      20 1983             1            1             0             0

14 rows selected.

Query-10 (ROW type windowing example)

The column FROM_PU_TO_CURR shows an example where start point of the window is before the current row and end point of the window is the current row. This column only has some real world significance. It can be thought of as the yearly employee build-up of the organization as each employee is getting hired.
The column FROM_P2_TO_P1 shows an example where start point of the window is before the current row and end point of the window is before the current row. This is a 3 row window and the count remains constant after it has got 3 previous rows.
The column FROM_F1_TO_F3 shows an example where start point of the window is after the current row and end point of the window is after the current row. This is a reverse of the previous column. Note how the count declines during the end.

RANGE Windows

For RANGE windows the general syntax is same as that of ROW:
Function( ) OVER (PARTITION BY <expr1> ORDER BY <expr2> RANGE BETWEEN <start_expr> AND <end_expr>)
or
Function( ) OVER (PARTITION BY <expr1> ORDER BY <expr2> RANGE [<start_expr> PRECEDING or UNBOUNDED PRECEDING]
For <start_expr> or <end_expr> we can use UNBOUNDED PECEDING, CURRENT ROW or <sql_expr> PRECEDING or FOLLOWING. However for RANGE type windows <sql_expr> must evaluate to value compatible with ORDER BY expression <expr1>.
<sql_expr> is a logical offset. It must be a constant or expression that evaluates to a positive numeric value or an interval literal. Only one ORDER BY expression is allowed.
If <sql_expr> evaluates to a numeric value, then the ORDER BY expr must be a NUMBER or DATE datatype. If <sql_expr> evaluates to an interval value, then the ORDER BY expr must be a DATE datatype.
Note the example (Query-11) below which uses RANGE windowing. The important thing here is that the size of the window in terms of the number of records can vary.

-- For each employee give the count of employees getting half more that their 
-- salary and also the count of employees in the departments 20 and 30 getting half 
-- less than their salary.
 
SELECT deptno, empno, sal,
Count(*) OVER (PARTITION BY deptno ORDER BY sal RANGE
BETWEEN UNBOUNDED PRECEDING AND (sal/2) PRECEDING) CNT_LT_HALF,
COUNT(*) OVER (PARTITION BY deptno ORDER BY sal RANGE
BETWEEN (sal/2) FOLLOWING AND UNBOUNDED FOLLOWING) CNT_MT_HALF
FROM emp
WHERE deptno IN (20, 30)
ORDER BY deptno, sal

 DEPTNO  EMPNO   SAL CNT_LT_HALF CNT_MT_HALF
------- ------ ----- ----------- -----------
     20   7369   800           0           3
     20   7876  1100           0           3
     20   7566  2975           2           0
     20   7788  3000           2           0
     20   7902  3000           2           0
     30   7900   950           0           3
     30   7521  1250           0           1
     30   7654  1250           0           1
     30   7844  1500           0           1
     30   7499  1600           0           1
     30   7698  2850           3           0

11 rows selected.

Query-11 (RANGE type windowing example)

Order of computation and performance tips

Defining the PARTITOIN BY and ORDER BY clauses on indexed columns (ordered in accordance with the PARTITION CLAUSE and then the ORDER BY clause in analytic function) will provide optimum performance. For Query-5, for example, a composite index on (deptno, hiredate) columns will prove effective.
It is advisable to always use CBO for queries using analytic functions. The tables and indexes should be analyzed and optimizer mode should be CHOOSE.
Even in absence of indexes analytic functions provide acceptable performance but need to do sorting for computing partition and order by clause. If the query contains multiple analytic functions, sorting and partitioning on two different columns should be avoided if they are both not indexed.

Conclusion

The aim of this article is not to make the reader try analytic functions forcibly in every other complex SQL. It is meant for a SQL coder, who has been avoiding analytic functions till now, even in complex analytic queries and reinventing the same feature much painstakingly by native SQL and join query. Its job is done if such a person finds analytic functions clear, understandable and usable after going through the article, and starts using them.

BI Intro

Form Personalization (Release 12)

The Form Personalization feature allows you to declaratively alter the behavior of Forms-based
screens, including changing properties, executing builtins, displaying messages, and adding
menu entries.

For each function (a form running in a particular context based on parameters passed to it), you can specify one or more Rules. Each Rule consists of an Event, an optional Condition, the Scope for which it applies, and one or more Actions to perform. 

Rules can be specified as acting either at the Function level (the default) or at the Form level. In
this latter case they are applied for all functions that run the form. When creating rules at the
Form level, pay close attention to the Sequence number and how it will blend with other rules at
the function level for the same form.

An Event is a trigger point within a form, such as startup (WHEN-NEW-FORM-INSTANCE), or
when focus moves to a new record (WHEN-NEW-RECORD-INSTANCE). There are standard
events that almost every form sends, and certain forms send additional product-specific events.

The Scope is evaluated based on the current runtime context to determine if a Rule should be
processed or not. The Scope can be at the Site, Responsibility, User, or Industry level. Each Rule
can have one or more Scopes associated with it.

NOTE: the scope of 'Industry' is reserved for future use. 

The Condition is an optional SQL code fragment that is evaluated when the Event occurs; if it

evaluates to TRUE then the Actions are processed.

Each Action consists of one of the following:
•setting a Property, such as making a field Required or hiding a Tab page
•executing a Builtin, such as GO_BLOCK, DO_KEY or FND_FUNCTION.EXECUTE
•displaying a Message
•enabling a menu entry

Once Rules are defined, when the target function is run then the Rules are automatically applied

as events occur within that form. 

 Although the Form Personalization feature is declarative, the intended audience is a person

familiar with Oracle Forms including the PL/SQL programming language, and the Oracle
Applications Development Guide. Additionally, any change made could interfere with the base
code of a form (the code that Oracle ships).

Using the Personalization Form

To create personalizations for a particular function, first invoke that function from the Navigation
menu. While in the form, choose Help->Diagnostics->Custom Code-> Personalize from the
pulldown menu. This menu entry is secured by the FND_HIDE_DIAGNOSTICS (Hide Diagnostics
menu entry) and DIAGNOSTICS (Utilities:Diagnostics) profiles, as are most other entries on the
Diagnostics menu.

The Personalization form will open and automatically query existing Rules for that function. After
making changes, Save them then close and re-run the function to have them take effect. You can
also Validate or Apply certain changes immediately to test them without having to re-run the
target form by pressing the ‘Validate’ or ‘Apply Now’ buttons.

The Personalization screen, when opened from the Users form (Function Name FND_FNDSCAUS)

 Each Rule consists of the following fields:

Seq: The sequence in which rules will be processed. This is a value between 1 and 100, with 1

being processed first. The sequence of rules does not have to be unique. Note that there is an

interaction with the Trigger Event field, described below. 

Description: Use this field to document the personalization you are making.

Enabled: Uncheck this checkbox to temporarily disable processing of a Rule. 

The following fields appear on the Condition tab

Trigger Event: Select the event at which you want the Rule to be processed. You can pick from

the list of standard events, or type in a specific event unique to the form. Note that this field is a Combobox, which is a hybrid of a Poplist and Text Item. Rules are processed first by matching the Event, then by their Sequence number.

Trigger Object: Depending on the Trigger Event, this field may be Disabled, or Enabled and

Required in which case it will validate against a List of Values. For example, if Trigger Event WHEN-NEW-ITEM-INSTANCE is selected, then you must enter a specificblock.field for that trigger to be processed.

Condition: This is an optional SQL code fragment that is evaluated when the Event occurs; if it

evaluates to TRUE then the Actions are processed. The condition can contain any of the

following:

•SQL functions and operators, such as AND, OR, TO_CHAR, DECODE, and NVL

•References to bind variables (:block.field), including :system, :global and :parameter values. Use

the 'Add Item...' button to assist with item names.

•Calls to server-side functions that do not have OUT parameters

The entire fragment must be syntactically correct, and can be tested with the 'Validate' button,
which will evaluate it in the current context of the target form. If the evaluation fails, the
processing engine will return an ORA error as if the condition had been part of a SQL expression.

Some examples:

:users.user_name is not null and  :users.description is null

The rule will be processed if the user_name field has a value and the description field does not

sysdate >= to_date(‘1-1-2011’, ‘DD- MM-RRRR )

The rule will be processed if the current date is equal toor after January 1, 2011. 

You can “Get” a property in your expression of your condition.
Conditions can refer to properties of objects using a SPEL syntax (Simplest Possible Expression
Language). For example, this enables you to build a Condition that tests if a field is displayed or
not. These expressions take the following general format:
${objectType.objectName.Property}
Internally, the SPEL expression is a cover for Oracle Forms builtins like GET_ITEM_PROPERTY,
GET_BLOCK_PROPERTY, etc. Additionally, the SPEL expressions support retrieving Profile
values, Message Dictionary text, and Local Variables (described later).

A new window, activated by the "Insert 'Get' Expression…" button, has been provided to
automatically construct the expression. When complete, the expression is inserted into the Value
or Condition field from where the window was launched. You can manually type the expression
too; however, we strongly recommend using the window to build it to reduce mistakes.

For completeness, the SPEL expression supports the 'Value' property for an item; however,

runtime performance is faster with the :block.field syntax.

 

All processing drives off the existence of the string '${', so that string is no longer valid in any
Condition or Value except for this purpose. If a Value contains a SPEL expression, it is processed
as if it started with '=' (See Evaluation of Strings, above).

  

Fire in Enter-Query Mode:Specify whether the Rule(s) should be applied while not in Enter-

Query mode (the default), only in Enter-Query mode, or in Both modes.

Each Rule consists of one or more Scope rows, and one or more Actions. If a Rule has no Scope rows or Action rows, it is not processed. Note that upon saving a Rule, if no Scope rows have been entered the form will automatically create a row at the Site level. If any scope matches the current runtime context then the Rule will be processed.

The following Scope fields appear in the Context region of the Condition tab:

Level: Select the level at which you want the rule to be applied, either Site, Responsibility, User,

or Industry.

Value: Based on the Level, either Disabled, or Enabled and Required in which case it will validateagainst a List of Values.  

All Action fields appear on the Actions tab

the Actions tab of the Personalization form

 Seq: The sequence in which actions will be processed within that Rule. This is a value between 1and 100, with 1 being processed first. The sequence does not have to be unique. All of the

actions associated with a particular rule are processed as a group, then the next rule (if any) is
processed. This feature is particularly useful when moving items, in which case a canvas may
have to be resized first before an X Position can be altered.

Type: the type of action to take:

•Property: allows you to select a specific object, a property of that object, and specify a new value

for that property

•Builtin: allows execution of a standard Forms Builtin, such as GO_BLOCK or DO_KEY
•Message: displays a message in one of several styles
•Menu: enables a special menu entry, defining its label, icon name and which blocks it applies to.

Description: Use this field to document the personalization action you are making.

Language: Specify 'All' to have the action processed for any language, or select a specific

language. Typically text-related personalizations would be applied for a specific language.

Enabled: Uncheck this checkbox to temporarily disable processing of the action.

Apply Now: For several Types, this button will be enabled. It allows you to apply the change

immediately to the target form to test its effect. Note that the actual effect that will occur during
normal runtime execution of rules may be different, due to timing of triggers and other
considerations.

The following buttons are enabled conditionally based on the Type field:

Add Parameter…: List of Values that displays currently used parameters. Applies to the builtin

FND_FUNCTION.EXECUTE only.

Add Block…: List of Values that displays block names.
Add Item…: List of Values that displays item names.
Validate:Used to test if the syntax of your string is valid. If the evaluation fails, the processing

engine will return an ORA error as if the string had been part of a SQL expression. Otherwise, it

will display the text exactly as it would appear at runtime in the current context.

The following fields appear conditionally based on the Type field:

For a Type of 'Property':

Select By Text: This button allows you to select an object based on text appearing on the screen

at the point in time that you invoke the Personalization form, including any changes that current rules might have performed. For example, if you want to change a field with the current prompt of 'Item Number', you should see 'Item Number' in this list, and selecting it will automatically fill in the Object Type and Target Object fields.

Note: As the visible text for objects may be identical across multiple windows of the same
form, it is sometimes difficult to determine exactly which object you wish to personalize.
The “Select By Text” list includes the window title in parentheses following the normal
object text.

  

Object Type: the type of object, including Item, Window, Block, Tab, Canvas, Radio button,

List of Value (LOV), View, Global variable, Local variable, or Parameter. A Local variable is similar to a Global variable, except that it creates a variable local to the current form, with a maximum length of 4000 bytes (Global variables are limited to 255 bytes).

Target Object: based on the Object Type, the internal name of the object. For Object Types of

GLOBAL and PARAMETER, the Target Object name must not include those keywords. For

example, to refer to GLOBAL.XX_MY_VARIABLE, only enter XX_MY_VARIABLE.

Note: If a Tab page does not appear in the list of objects its name can be entered manually. This is meant to address the known limitation that some tab names cannot be automatically detected at runtime. You will need to open the form in the Oracle Forms Builder to determine these tab names, and then you can type it into this Combobox.

 

Property Name: based on the Object Type, the properties that can be personalized. The

Object Type of Item supports a vast array of properties including:

•

Item-level properties, which set the property for all instances of that object.

•

Item-instance properties, which set the property for the current record of that

block using set_item_instance_property()

•

Applications cover properties, which are a hybrid of multiple item and item-
instance level properties. These are fully documented in the Oracle Applications
Developer’s Guide.

For the LOV object type, simple LOV properties can be modified using Form Personalization, including GROUP_NAME, HEIGHT, TITLE, WIDTH, X_POS and Y_POS. At this point in time, the property RECORD_GROUP_QUERY, which would allow you to dynamically change the underlying query of an LOV, cannot be changed; this is being considered for a future release

For the Block object type, the property ALLOW_NON_SELECTIVE_SEARCH is provided.
For those blocks where a “Blind Query” has been prevented by development for performance
reasons, this new property allows you to re-enable “Blind Query” on a block-by-block basis.

Also for the Block object type, the property EXPORT_HIDDEN_CANVASES allows for
exporting block data that is on hidden canvases. See: Exporting Data on Hidden Canvases,
below.

Value: the new value. The appearance and validation of this field changes based on whether

the property accepts Boolean values (True/False), numbers, a restricted set of values, or a

string (See Evaluation of Strings below)

 

Get Value:This button gets the current property value of the object.   

For a Type of 'Message': 

Message Type: either 'Show', 'Hint', ‘Warn’, 'Error', or ‘Debug’. ‘Error’ and ‘Warn’ if the user

selects the ‘Cancel’ button will raise a form_trigger_failure after executing, and stop all further
processing. Messages of type ‘Debug’ will only be displayed if the ‘Show Debug Messages’
checkbox is checked.

Message Text: The text you want to display in the message.

For a Type of 'Builtin':

Builtin Type: The name of the builtin. Examples include:

•GO_ITEM

•DO_KEY

•GO_BLOCK

•RAISE FORM_TRIGGER_FAILURE

•FORMS_DDL

•‘Launch a URL (formerly called FND_UTILITIES.OPEN_URL). This builtin allows an HTML target window to be set. By default the target is '_BLANK', which will open a new browser window. You can select from 4 pre-defined targets, or enter your own target name.

•‘Launch a Function’ (formerly called FND_FUNCTION.EXECUTE). The function can

be selected by either its internal (developer) name or its user-friendly name.

•‘Launch SRS Form’ allows the Submit Concurrent Request form (SRS) to be run in

the context of a specific program. This takes a single argument of the program name.
Note that no additional security is applied to this program name – whatever you supply will be available to the user, even if a standard invocation of the SRS screen does not allow access to that program.

•‘Execute Procedure’ allows a stored procedure to be executed. Type the procedure name and arguments exactly as you would in PL/SQL code. The entire string will be evaluated before being sent to the server for processing. See “Evaluation of Strings” below. Internally, this uses the FORMS_DDL builtin, but automatically supplies the additional PL/SQL wrapper text.

•SYNCHRONIZE is intended for rare circumstances when the screen is not synchronized between the client and middle tiers, typically at form startup

EXECUTE_TRIGGER allows you to execute a trigger within the form. It takes an argument of the trigger name. This builtin will test form_success after completion, and if that is False then it will raise form_trigger_failure.

•Call Custom Event’ allows the CUSTOM library to be called. Despite the declarative

interface of Forms Personalization it is still sometimes necessary to write PL/SQL
code within the CUSTOM library. To facilitate this we have introduced a new builtin
type of ‘Call Custom Event’ which takes a single argument of the event name. Within
the CUSTOM library, code can then be written to respond to this event.

•‘Create Record Group from Query’ allows you to create a record group dynamically. This takes two arguments, the SQL statement to create the group and the record group name which must not already exist within the form. This builtin can be used to declaratively create a record group that can then be used to populate an LOV. Note that using this builtin requires knowledge of the structure of the existing record group in order to achieve success. The structure of the current record group assigned to the LOV can only be determined by opening the form in Forms Builder, determining the correct record group, and then looking at the SQL statement. Also note that all this routine does is create a record group – it does not populate the group 

Argument: The argument for the currently selected builtin, if applicable.

Depending on the specific builtin, other argument fields may appear.

Function Name: The name of the function that should be executed.   

Parameters: You can manually enter parameters or use the ‘Add Parameter…’ button. The

‘Add Parameter…’ button displays an LOV listing currently defined parameters for the Function Name, by querying other functions for that same form. It is possible that other parameters exist that will not be displayed in the LOV. The only way to see all parameters that a function has is to open the form in the Oracle Forms Builder. Oracle makes no warranties that any function provides the specific input parameters that you may desire, nor that any existing parameter and its behavior will remain unchanged after a patch.

For a Type of 'Menu'

Menu Entry:A menu entry that you can activate. If you select a menu that the base form is

already using, your functionality will override the form's functionality.

Menu Label: The textual label that you want on the menu entry. (See Evaluation of Strings,

below).

Render line before menu: If checked, will render a line above the menu entry to visually

separate it from prior entries.

Enabled in Block(s): Specify the blocks that you want the menu entry enabled in; specify

more than one block by separating the names with a comma. If no blocks are specified, the
entry will be enabled in all blocks. Use the 'Add Block...' button to add a blockname to the end
of the field.

Icon Name: Specify an optional icon name that you want added to the Toolbar to achieve the

same functionality as in the special pulldown menu entry.

Specifying an action of 'Menu' (formerly called ‘Special’) merely activates the appropriate
menu entry. When the user selects the entry, it will fire the corresponding MENU# trigger. You
must also create another rule that traps this Trigger Event and performs the desired
functionality.

Note that the Actions block automatically defaults some values from the prior row when you
create a new row. In the initial release of Form Personalization, the Actions block used to
automatically default most values from the prior row when you created a new row. Now a
smaller number of properties is defaulted, in order to reduce confusion. To copy all the values
from the prior row, use the menu entry Edit > Duplicate > Record Above.

Evaluation of Strings

Every property that takes a string can either be processed literally or evaluated at runtime.

•If you type a string in that does not start with ‘=’, then the exact value you type in will be

used at runtime

•If the string you type starts with ‘=’, then the text immediately after that character will be

evaluated at runtime. This allows you to write complex logic that can include references

such as:

•SQL operators, such as ||, TO_CHAR, DECODE, and NVL

•bind variables (:block.field), including :system, :global and :parameter values. Use the

'Add Item...' button to assist with item names.

•Calls to server-side functions that do not have OUT parameters.

•SELECT statements. To use this form, you must follow these rules:

•The text must start with '=SELECT'

•The column being selected must evaluate to a CHAR, with a length no longer than

2000 bytes.

•Your SELECT statement should only return one row, but if more than one is returned

only the value of the first row will be used.

•In the initial release of Form Personalization, you needed to alias the column being

selected to 'A'. That is no longer necessary, although pre-existing aliased text will continue to function  as before. However, there is the additional limitation that the result set MUST return only a single row and only a single column. If this is not the case then an error will be shown during processing.

 

When using this method you must follow SQL conventions for string processing, including escaping of quotes. The following examples show how this can be used: 

Use the ‘Validate’ button to test if the syntax of your string is valid. If the evaluation fails, the processing engine will return an ORA error as if the string had been part of a SQL expression. Otherwise, it will display the text exactly as it would appear at runtime in the current context.

Strings, (like Conditions described above) can now refer to properties of objects using a SPEL syntax (Simplest Possible Expression Language). For example, this enables you to build a Condition that tests if a field is displayed or not. These expressions take the following general format:
${objectType.objectName.Property}
Internally, the SPEL expression is a cover for Oracle Forms builtins like GET_ITEM_PROPERTY,
GET_BLOCK_PROPERTY, etc. Additionally, the SPEL expressions support retrieving Profile
values, Message Dictionary text, and Local Variables (described later).

A new window, activated by the "Insert 'Get' Expression…" button, has been provided to automatically construct the expression. When complete, the expression is inserted into the Value or Condition field from where the window was launched. You can manually type the expression too; however, we strongly recommend using the window to build it to reduce mistakes.

For completeness, the SPEL expression supports the 'Value' property for an item; however,

runtime performance is faster with the :block.field syntax. All processing drives off the existence of the string '${', so that string is no longer valid in anyCondition or Value except for this purpose. If a Value contains a SPEL expression, it is processedas if it started with '=' (See Evaluation of Strings, above).

Exporting data on Hidden canvases

When exporting data from a forms block, only the data that is currently displayed for the block is
exported. If you wish to allow exporting the block data that is on other hidden canvases, as is
often with tab pages, you can create a form personalization rule to allow exporting the hidden
canvases.

To create a personalization to allow exporting hidden canvases, create a new personalization as

follows:

Condition
Trigger Event: WHEN-NEW-FORM-INSTANCE level.
Processing Mode: Not in Enter-Query-Mode.
Set the appropriate Context Level.

Actions
Type: Property
Object Type: Block
Target Object: <the block for the export personalization>
Property Name: EXPORT_HIDDEN_CANVASES
Value: TRUE

Limitations

This feature has several significant limitations due to the architecture of Oracle Forms and/or the

E-Business Suite.

You can only change what Oracle Forms allows at runtime. For example, the following cannot be

changed:

•You cannot create new items
•You cannot move items between canvases
•You cannot display an item which is not on a canvas (thus, individual flexfield

segments cannot be displayed)

•You cannot set certain properties such as the Datatype of an Item.
•You cannot change frames, graphics, or boilerplate
•You cannot hide the item that currently has focus

Form Personalization can only respond to events that are centrally processed and dispatched by

APPCORE. These are limited to:

•WHEN-NEW-FORM-INSTANCE, WHEN-NEW-BLOCK-INSTANCE, WHEN-NEW-

RECORD-INSTANCE, WHEN-NEW-ITEM-INSTANCE. These events occur as the

user moves focus within the form.

•WHEN-VALIDATE-RECORD (in many but not all forms). This event occurs whenever changes have been made to the current record in the current block.

•MENU1 through MENU15 menu entries, available in the Tools pulldown menu. These are guaranteed not to be used by Oracle and are exclusively for customer use, therefore we strongly encourage you to use these entries as opposed to the SPECIAL menu entries in order to avoid collisions of code. When the user selects the entry, it will fire the corresponding MENU# trigger. You must also create another rule that traps

this Trigger Event and performs the desired functionality.

•Product-specific events. These are typically documented in implementation manuals, such as 'Configuring, Reporting and System Administration in Oracle HRMS'.

You can see events that are being passed by enabling the 'Show Events' option in the Custom

Code menu. Certain personalizations must be performed at specific events:

•To specify the Initial Value of an Item, you must perform that action in the WHEN-NEW-RECORD-INSTANCE event of the block that contains the item.

•MENU# menu entries can only be created at form startup (WHEN-NEW-FORM-INSTANCE)

Both the Personalization form and the runtime processing engine will report errors for these

cases and skip processing of them. Certain objects may not be available to you to change, or cannot be validated.

Specifically, the object types GLOBAL and PARAMETER cannot be detected, thus these fields

have no LOVs to restrict their input. Use the 'Validate' or 'Apply Now' buttons to determine if the values you have entered actually exist. Note that GLOBAL variables are dynamically created, so whether they exist or not can be a matter of timing.

Note: If a Tab page does not appear in the list of objects its name can be entered manually. This is meant to address the known limitation that some tab names cannot be automatically detected at runtime. You will need to open the form in the Oracle Forms Builder to determine these tab names, and then you can type it into this Combobox.

 

Most significantly, any change you make might interfere with the normal operation of the form.

This can manifest itself in several ways, such as:

•You may make a personalization but it doesn't take effect, because there is code in

the form that overrides it. In some cases you may be able to perform your
personalization by moving the Trigger Event to a 'lower' level, such as block- or item-
level.

•Your personalization may simply produce the wrong result, because your change

interacted with the base code in unexpected and untested ways. At best this error will
occur immediately upon the personalization being applied; at worst it could affect
some later processing which does not appear to be directly related to the object or
event.

•In extreme cases, your changes may prevent the form from running at all, making it

difficult to open the Personalization screen to remove the offending personalization

unless you turn off Custom Code.
Because of this, it is critical that any change be thoroughly tested in a Test environment. See the
'Troubleshooting, Support, and Upgrade considerations' section later in this chapter for more
information.

Examples and Tips

Changing the prompt of an item
This is a step-by-step example of changing a prompt. In this case, we will modify the 'Users' form,
and change the prompt ‘User Name’ to ‘Logon Name’:

1. Open the Users form

2.Select Help->Diagnostics->Custom Code-> Personalize from the pulldown menu. If thismenu entry is disabled, check the values of the FND_HIDE_DIAGNOSTICS and DIAGNOSTICS profiles.

3. Create a rule with the following values:  

Seq:1

Description: Change prompt of User Name

Accept the defaults for all other values of the Rule and Context

4. Select the Actions Tab and enter the following values:

Seq:1
Press the ‘Select By Text’ button and choose the ‘User Name’ row from the LOV
Property Name: PROMPT_TEXT
Value: Logon Name

Accept the defaults for all other values of the Actions.

5. Save

6. Activate the Users form, then close it.
7. Re-open the Users form. You should see that the prompt is now ‘Logon Name’.
8. To disable this Rule, set Enabled to unchecked (at either the Rule or Action level), or just

delete the Rule, then Save.

 

Disabling or Hiding a Tab Page
When you disable or hide a tab page, it does not change the state of the items on that page. Most
significantly, if the items remain Navigable, and there is code elsewhere that attempts to navigate
to them, it will succeed, causing the tab page to appear. To completely achieve the effect of hiding
or disabling a tab page, you may need to account for the following:

The ‘Next Navigation Item’ of the item that immediately precedes the first item on the tab

page.

The ‘Previous Navigation Item’ of the item(s) that immediately follow the last item on the

tab page.

The ‘Next Navigation Block’ of the block that immediately precedes the block that

contains an item on the tab page.

The ‘Previous Navigation Block’ of the block(s) that immediately follow the block that

contains an item on the tab page.
Depending on how the form was coded, there may be additional properties or events that need to
be changed

  

Messages are a great debugging tool
Due to interactions of Personalization and the base code of each form, it is common to create
rules that do not seem to be getting applied, or are applied incorrectly.
The simplest way to debug is to include Message actions of type ‘Debug’ either immediately
before or after the action of interest.

Debug Mode supports two levels. 'Show Debug Messages' will show messages of type Debug.
The ‘Step-By-Step' mode that will show messages when each condition is evaluated and each
action is executed, to help you determine if your rules and actions are being applied as expected.
'Off' disables these modes

If you do not see your debug message at all, then the most likely reasons are:

-The Rule or Action is not enabled
-The Condition you entered for the Rule has evaluated to FALSE
-The Trigger Event and/or Trigger Object were not what you expected
-The scope of the rule only consists of Responsibility, Industry and/or User, and none is

true for the current context.

-An action is executing the Builtin 'RAISE FORM_TRIGGER_FAILURE' . That will abort

all further processing for that event.

-The Language of the Action is different than what you are currently running

-You have set Custom Code to ‘Off’ or ‘Core code only’ in the pulldown menu.

 

Interaction with the ‘Close Other Forms’ checkbox
While building personalizations, you may need to exit and re-open your form in order to see the
effect of your personalizations. We recommend that you set the ‘Close Other Forms’ option in the
Tools menu of the Navigator to unchecked, otherwise the Personalization form itself will close

automatically before your form runs.

Using the same value multiple times
Often in code you want to determine a value once, then refer to it multiple times. You can do that
with Form Personalization by using the 'Value' property of a GLOBAL variable. You can set such a
value using any of the methods mentioned in 'Evaluation of Strings'. By setting that property, the
global variable will be created at that point in time, and then any future actions can refer to that
variable. To minimize the risk of collision with GLOBAL variables that the base form may create,
name your variable starting with 'XX'.

Zooming to a form
A common scenario is to open another function and have that form query a specific row of data
associated with the source form. For example, consider the Users form which allows entry of
Responsibilities for each user. A possible zoom would be to carry the current responsibility key to
the Responsibilities form and have it query that record automatically. Some strategies to
accomplish this type of functionality are:

-The target function may already accept the value as an input parameter. Simply passing

the parameter name and value as the argument to the Function may accomplish the

desired result.

-In forms that have Find windows, it may be possible to populate the appropriate field in

the Find window, then issue DO_KEY('NEXT_BLOCK') which should simulate pressing

the Find Window. Pass the value(s) between forms by using global variables.

-You could modify the DEFAULT_WHERE clause of the appropriate block, then query it

using DO_KEY('EXECUTE_QUERY'), then reset the DEFAULT_WHERE clause back to

its original value. Pass the value(s) between forms by using global variables.

-Put the form into enter-query mode using DO_KEY('ENTER_QUERY'), populate the

desired fields, then issue DO_KEY('EXECUTE_QUERY'). Pass the value(s) between
forms by using global variables. This is a complex technique though because invoking
enter-query mode will suspend processing of that rule; populating the fields and issuing
the DO_KEY('EXECUTE_QUERY') would need to be done in the WHEN-NEW-
RECORD-INSTANCE event that fires as a result of entering query-mode.

With any technique that uses global variables, be aware that they may not exist yet. You should always initialize them before referring to them by setting the Initial Value to null, which will create the variable if it does not yet exist, otherwise it will leave it unchanged.

You should also code Conditions to account for situations where the value you want to pass between forms has not yet been entered, for example when the cursor is sitting on a brand new row.

 

Administration Window

The ‘Find Personalizations’ administration window can be invoked only from the Personalization Form. In the Tools pulldown menu, select ‘Administration’. This will allow you to get a list of all functions that currently have Personalizations (Rules) defined.

Form: The filename of a form.

If you press the Find button while Form Name is empty, all forms that have any personalizations
(enabled or not) will be queried. This is particularly useful after applying a patch; knowing which
forms the patch affects, you can quickly determine if any personalizations need to be re-validated.

Enabled Rules: The number of active rules that a function has defined. 

Moving Personalizations between instances

Once you create and test personalizations in your test instance, you can move them to production 

instances. Personalizations can beextracted by the loader on a per-function basis or per-form
basis (that is, each loader file will contain all of the personalizations for a single function or form,
respectively). Note that upon uploading, all prior personalizations for that function are first
deleted, and then the contents of the loader file are inserted.

The loader syntax is as follows:

To download rules for a particular function:

FNDLOAD <userid>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct

<filename.ldt> FND_FORM_CUSTOM_RULES function_name=<function name> 

Note: this style is not recommended, as the personalizations that affect a particular

function can now be a mix of function- and form-level rules.

To download rules for a particular form:

FNDLOAD <userid>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct

<filename.ldt> FND_FORM_CUSTOM_RULES form_name=<form name>

To download all personalizations (all forms and functions):

FNDLOAD <userid>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct

<filename.ldt> FND_FORM_CUSTOM_RULES

Upload:

FNDLOAD <userid>/<password> 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct

<filename.ldt> 

Relationship to CUSTOM library

Form Personalization allows personalizations that could be made in the CUSTOM library, but it
does not require that you use the Oracle Forms Builder to edit and compile the CUSTOM file.
Depending on the complexity of your personalizations, it may still require a degree of coding skill
comparable to that needed to use the CUSTOM library. And the CUSTOM library is able to
support more complex personalizations because it gives you access to all of the capabilities of
the PL/SQL programming language, including calling client-side program units, all Oracle Forms
builtins, and issuing any SQL.

Both Form Personalization and the CUSTOM library drive off the exact same events. The Form Personalization feature receives and processes them first, then passes them to the CUSTOM library, thus you can use both mechanisms simultaneously. (Note: SPECIALXX and MENUXX events are not passed to the CUSTOM library).

Both features also respond identically to the Custom Code events of 'Normal', 'Off' and 'Core

Code Only'.

In general, Oracle recommends that you use the Form Personalization feature whenever
possible, and only use the CUSTOM library when significantly more complex processing is
required.

Relationship to Folders

Folders allow an end-user to 'customize' a screen by changing the fields and records displayed. For the most part, folder blocks are identifiable by an enabled ‘Folder’ menu entry, and an ‘Open Folder’ icon above the block. In a few cases, folder technology may be used by base code to dynamically alter a block, but no folder functionality is exposed to the end user.

Folder blocks are constructed differently than ‘regular’ Forms blocks – they include an extra block
that renders the prompts for the fields, and many properties of the block are dynamically
managed by the folder code as it receives events. As a result, when using the Form
Personalization feature on a folder block, you must be aware of certain restrictions:

The following properties of a folder block can only be set at form startup (WHEN-NEW-
FORM-INSTANCE). More specifically, they must be set before any folder code attempts to
read the values otherwise unexpected results may occur:

-PROMPT_TEXT
-DISPLAYED
-WIDTH
-DEFAULT_WHERE
-ORDER_BY
-X_POSITION and Y_POSITION, in a single-row folder block

The following properties also have special considerations:

-ENABLED: within a folder block, it is invalid to set this property to FALSE. The cursor

must be able to navigate to every item in a folder block. Consider setting ALTERABLE to

FALSE instead.

-NEXT_ NAVIGATION_ITEM and PREVIOUS_NAVIGATION_ITEM: These properties

have no effect in a Folder block. In a single-row folder block, the navigation sequence is
computed based on X_POSITION and Y_POSITION. The navigation sequence of a
multi-row folder block cannot be changed

Troubleshooting, Support, and Upgrade considerations

Using the Form Personalization feature to alter Oracle code at runtime may bypass important
validation logic and may jeopardize the integrity of your data. You should thoroughly test all
changes you make in a Test instance before using it in a production environment.

Before contacting Oracle Support, you should always confirm that your personalizations are not
the source of the problem. Oracle Support and Development cannot provide assistance on how
you can make personalizations to a particular form using this mechanism, nor does Oracle
warrant that any desired personalization can be made with this mechanism.

Troubleshooting
Any personalization you make may have unintentional consequences, to an extreme of
preventing a form from running at all. Should this happen, you can disable all personalizations by
invoking the pulldown menu and selecting Help->Diagnostics->Custom Code-> Off. This menu
entry is secured by the FND_HIDE_DIAGNOSTICS and DIAGNOSTICS profiles. This will allow
you to open the form and invoke the Personalization screen so you can correct the problem.

Upgrade Considerations
A form may change after an upgrade or patch to Oracle Applications. You should test any
personalization logic that you have defined to confirm that it still operates as intended before
using it in a production environment.

It is common for object names within a form to change after a applying a patch. To assist you with
this, there is a function which will confirm the existence of objects that your personalizations
reference. You should perform the following for each form that is changed during a patch and has
personalizations:

-Run that form. If the form fails to run due to personalizations that are now in error, first

turn Custom Code to ‘Off‘ then re-run the form.

-Invoke the Personalization screen from that form