Using the MODEL Clause

You use the new Oracle Database 10 g MODEL clause to perform inter-row calculations. The MODEL clause allows you to access a column in a row like a cell in an array. This gives you the ability to perform calculations in a similar manner to spreadsheet calculations. For example, the all_sales table contains sales information for the months in 2003. You can use the MODEL clause to calculate sales in future months based on sales in 2003.

An Example of the MODEL Clause

The easiest way to learn how to use the MODEL clause is to see an example. The following query retrieves the sales amount for each month in 2003 made by employee #21 for product types #1 and #2, and computes the predicted sales for January, February, and March of 2004 based on sales in 2003:

 SELECT prd_type_id, year, month, sales_amount FROM all_sales WHERE prd_type_id BETWEEN 1 AND 2 AND emp_id = 21 MODEL PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) (sales_amount[1, 2004] = sales_amount[1, 2003],  sales_amount[2, 2004] =  sales_amount[2, 2003] + sales_amount[3, 2003],  sales_amount[3, 2004] = ROUND(sales_amount[3, 2003] * 1.25, 2)) ORDER BY prd_type_id, year, month; 

Let s break this query down:

• PARTITION BY (prd_type_id) specifies the results are partitioned by prd_type_id .

• DIMENSION BY (month, year) specifies the dimensions of the array are month and year . This means you access a column in a row by supplying a month and year.

• MEASURES (amount sales_amount) specifies each cell in the array contains an amount, and the array name is sales_amount . To access the cell in the sales_amount array for January 2003, you use sales_amount[1, 2003] , which returns an amount.

• After MEASURES come three lines that compute the future sales for January, February, and March of 2004:

  • sales_amount[1, 2004] = sales_amount[1, 2003] sets the sales amount for January 2004 to the amount for January 2003.

  • sales_amount[2, 2004] = sales_amount[2, 2003] + sales_amount[3, 2003] sets the sales amount for February 2004 to the amount for February 2003 plus March 2003.

  • sales_amount[3, 2004] = ROUND(sales_amount[3, 2003] * 1.25, 2) sets the sales amount for March 2004 to the rounded value of the sales amount for March 2003 multiplied by 1.25.

• ORDER BY prd_type_id, year, month simply orders the results returned by the entire query.

The output from the example query is as follows . Notice the results contain the sales amounts for all months in 2003 for product types #1 and #2, plus the predicted sales amounts for the first three months in 2004 (which I ve made bold to make them stand out):

 PRD_TYPE_ID  YEAR MONTH SALES_AMOUNT ----------- ---------- ---------- ------------    1  2003    1  10034.84    1   2003   2  15144.65     1   2003    3  20137.83    1   2003     4   25057.45     1   2003    5  17214.56    1   2003   6   15564.64    1   2003   7   12654.84     1  2003    8  17434.82      1   2003    9  19854.57     1   2003    10  21754.19     1  2003    11 13029.73     1   2003    12  10034.84  1    2004    1  10034.84   1   2004     2  35282.48   1   2004    3  25172.29  2   2003    1   1034.84     2   2003   2   1544.65     2   2003    3   2037.83    2  2003    4   2557.45     2  2003   5  1714.56     2   2003   6  1564.64     2   2003    7   1264.84      2   2003    8  1734.82    2   2003  9   1854.57     2   2003   10   2754.19    2  2003     11  1329.73     2   2003   12   1034.84  2  2004    1  1034.84   2   2004     2   3582.48   2   2004   3   2547.29  

Using Positional and Symbolic Notation to Access Cells

In the previous example, you saw how to access a cell in an array using the following notation: sales_amount[1, 2004] , where 1 is the month and 2004 is the year. This is referred to as positional notation because the meaning of the dimensions is determined by their position; the first position contains the month and the second position contains the year.

You can also use symbolic notation to explicitly indicate the meaning of the dimensions, for example, sales_amount[month=1, year=2004] . The following query rewrites the previous example to use symbolic notation:

 SELECT prd_type_id, year, month, sales_amount FROM all_sales WHERE prd_type_id BETWEEN 1 AND 2 AND emp_id = 21 MODEL PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) (sales_amount[month=1, year=2004] = sales_amount[month=1, year=2003],  sales_amount[month=2, year=2004] =  sales_amount[month=2, year=2003] + sales_amount[month=3, year=2003],  sales_amount[month=3, year=2004] =  ROUND(sales_amount[month=3, year=2003] * 1.25, 2)) ORDER BY prd_type_id, year, month; 

One difference to be aware of when using positional or symbolic notation is how they handle null values in the dimensions. sales_amount[null, 2003] returns the amount whose month is null and year is 2003, but sales_amount[month=null, year=2004] won t access a valid cell because null=null always returns false.

Accessing a Range of Cells Using BETWEEN and AND

You can access a range of cells using the BETWEEN and AND keywords. For example, the following expression sets the sales amount for January 2004 to the rounded average of the sales between January and March of 2003; notice the use of BETWEEN and AND :

 sales_amount[1, 2004] =  ROUND(AVG(sales_amount)[month BETWEEN 1 AND 3, 2003], 2) 

The following query shows the use of this expression:

 SELECT prd_type_id, year, month, sales_amount FROM all_sales WHERE prd_type_id BETWEEN 1 AND 2 AND emp_id = 21 MODEL PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) (sales_amount[1, 2004] =  ROUND(AVG(sales_amount)[month BETWEEN 1 AND 3, 2003], 2)) ORDER BY prd_type_id, year, month; 

Accessing All Cells Using ANY and IS ANY

You can access all cells using the ANY and IS ANY predicates. You use ANY with positional notation and IS ANY with symbolic notation. For example, the following expression sets the sales amount for January 2004 to the rounded sum of the sales for all months and years ; notice the use of ANY and IS ANY :

 sales_amount[1, 2004] =  ROUND(SUM(sales_amount)[ANY, year IS ANY], 2) 

The following query shows the use of this expression:

 SELECT prd_type_id, year, month, sales_amount FROM all_sales WHERE prd_type_id BETWEEN 1 AND 2 AND emp_id = 21 MODEL PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) (sales_amount[1, 2004] =  ROUND(SUM(sales_amount)[ANY, year IS ANY], 2)) ORDER BY prd_type_id, year, month; 

Getting the Current Value of a Dimension Using CURRENTV()

You can get the current value of a dimension using the CURRENTV() function. For example, the following expression sets the sales amount for the first month of 2004 to 1.25 times the sales of the same month in 2003. Notice the use of CURRENTV() to get the current month, which is 1:

 sales_amount[1, 2004] =  ROUND(sales_amount[CURRENTV(), 2003] * 1.25, 2) 

The following query shows the use of this expression:

 SELECT prd_type_id, year, month, sales_amount FROM all_sales WHERE prd_type_id BETWEEN 1 AND 2 AND emp_id = 21 MODEL PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) (sales_amount[1, 2004] =  ROUND(sales_amount[CURRENTV(), 2003] * 1.25, 2)) ORDER BY prd_type_id, year, month; 

The output from this query is as follows; I ve highlighted the values for 2004 in bold:

 PRD_TYPE_ID  YEAR  MONTH SALES_AMOUNT ----------- ---------- ---------- ------------     1   2003   1  10034.84     1   2003    2  15144.65    1   2003    3 20137.83      1   2003   4   25057.45     1   2003    5  17214.56     1    2003   6  15564.64    1   2003    7  12654.84     1   2003    8  17434.82     1    2003   9  19854.57     1  2003    10   21754.19     1   2003    11  13029.73    1   2003    12  10034.84  1   2004   1   12543.55  2   2003   1  1034.84    2   2003   2   1544.65     2  2003    3  2037.83    2   2003    4   2557.45     2   2003    5   1714.56     2   2003    6  1564.64     2  2003    7   1264.84     2   2003   8   1734.82     2   2003     9  1854.57      2  2003   10  2754.19     2   2003  11   1329.73     2  2003    12  1034.84  2    2004   1  1293.55  

Accessing Cells Using a FOR Loop

You can access cells using a FOR loop. For example, the following expression sets the sales amount for the first three months of 2004 to 1.25 times the sales of the same months in 2003. Notice the use of the FOR loop and the INCREMENT keyword that specifies the amount to increment month by during each iteration of the loop:

 sales_amount[FOR month FROM 1 TO 3 INCREMENT 1, 2004] =  ROUND(sales_amount[CURRENTV(), 2003] * 1.25, 2) 

The following query shows the use of this expression:

 SELECT prd_type_id, year, month, sales_amount FROM all_sales WHERE prd_type_id BETWEEN 1 AND 2 AND emp_id = 21 MODEL PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) (sales_amount[FOR month FROM 1 TO 3 INCREMENT 1, 2004] =  ROUND(sales_amount[CURRENTV(), 2003] * 1.25, 2)) ORDER BY prd_type_id, year, month; 

The output from this query is as follows; I ve highlighted the values for 2004 in bold:

 PRD_TYPE_ID   YEAR MONTH SALES_AMOUNT ----------- ---------- ---------- ------------     1  2003   1   10034.84     1  2003   2  15144.65      1  2003   3   20137.83     1  2003    4  25057.45      1   2003  5 17214.56     1    2003    6  15564.64    1   2003   7   12654.84     1   2003   8  17434.82     1  2003    9  19854.57    1   2003  10 21754.19    1  2003   11   13029.73     1   2003   12   10034.84  1  2004     1  12543.55   1   2004    2  18930.81   1  2004     3   25172.29  2  2003   1 1034.84    2  2003    2  1544.65     2  2003    3  2037.83    2  2003    4   2557.45     2  2003     5   1714.56    2  2003    6  1564.64     2  2003   7  1264.84     2   2003   8  1734.82      2   2003    9   1854.57     2  2003  10   2754.19     2   2003   11   1329.73      2 2003     12  1034.84  2  2004     1  1293.55   2   2004    2 1930.81   2    2004    3  2547.29  

Handling Null and Missing Values

In this section, you ll learn how to handle null and missing values using the MODEL clause.

Using IS PRESENT

IS PRESENT returns true if the row specified by the cell reference existed prior to the execution of the MODEL clause. For example:

 sales_amount[CURRENTV(), 2003] IS PRESENT 

will return true if sales_amount[CURRENTV(), 2003] exists.

The following expression sets the sales amount for the first three months of 2004 to 1.25 times the sales of the same months in 2003; notice the use of IS PRESENT :

 sales_amount[FOR month FROM 1 TO 3 INCREMENT 1, 2004] =  CASE WHEN sales_amount[CURRENTV(), 2003] IS PRESENT THEN  ROUND(sales_amount[CURRENTV(), 2003] * 1.25, 2)  ELSE  0  END 

The following query shows the use of this expression:

 SELECT prd_type_id, year, month, sales_amount FROM all_sales WHERE prd_type_id BETWEEN 1 AND 2 AND emp_id = 21 MODEL PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) (sales_amount[FOR month FROM 1 TO 3 INCREMENT 1, 2004] =  CASE WHEN sales_amount[CURRENTV(), 2003] IS PRESENT THEN  ROUND(sales_amount[CURRENTV(), 2003] * 1.25, 2)  ELSE  0  END) ORDER BY prd_type_id, year, month; 

The output of this query is the same as the example in the previous section.

Using PRESENTV()

PRESENTV( cell , expr1 , expr2 ) returns the expression expr1 if the row specified by the cell reference existed prior to the execution of the MODEL clause. If the row doesn t exist, the expression expr2 is returned. For example:

 PRESENTV(sales_amount[CURRENTV(), 2003],  ROUND(sales_amount[CURRENTV(), 2003] * 1.25, 2), 0) 

will return the rounded sales amount if sales_amount[CURRENTV(), 2003] exists; otherwise , 0 will be returned.

The following query shows the use of this expression:

 SELECT prd_type_id, year, month, sales_amount FROM all_sales WHERE prd_type_id BETWEEN 1 AND 2 AND emp_id = 21 MODEL PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) (sales_amount[FOR month FROM 1 TO 3 INCREMENT 1, 2004] =  PRESENTV(sales_amount[CURRENTV(), 2003],   ROUND(sales_amount[CURRENTV(), 2003] * 1.25, 2), 0)) ORDER BY prd_type_id, year, month; 

Using PRESENTNNV()

PRESENTNNV( cell , expr1 , expr2 ) returns the expression expr1 if the row specified by the cell reference existed prior to the execution of the MODEL clause and the cell value is not null. If the row doesn t exist or the cell value is null, the expression expr2 is returned. For example:

 PRESENTNNV(sales_amount[CURRENTV(), 2003],  ROUND(sales_amount[CURRENTV(), 2003] * 1.25, 2), 0) 

will return the rounded sales amount if sales_amount[CURRENTV(), 2003] exists and is not null; otherwise, 0 will be returned.

Using IGNORE NAV and KEEP NAV

IGNORE NAV returns

• 0 for null or missing numeric values

• An empty string for null or missing string values

• 01-JAN-2000 for null or missing date values

KEEP NAV returns null for null or missing numeric values.

Note  

KEEP NAV is the default.

For example:

 SELECT prd_type_id, year, month, sales_amount FROM all_sales WHERE prd_type_id BETWEEN 1 AND 2 AND emp_id = 21 MODEL IGNORE NAV PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) (sales_amount[FOR month FROM 1 TO 3 INCREMENT 1, 2004] =  ROUND(sales_amount[CURRENTV(), 2003] * 1.25, 2)) ORDER BY prd_type_id, year, month; 

Updating Existing Cells

By default, if the cell referenced on the left side of an expression exists, it is updated. If the cell doesn t exist, a new row in the array is created. You can change this default behavior using RULES UPDATE , which specifies that if the cell doesn t exist, don t create a new row.

The following query uses RULES UPDATE :

 SELECT prd_type_id, year, month, sales_amount FROM all_sales WHERE prd_type_id BETWEEN 1 AND 2 AND emp_id = 21 MODEL PARTITION BY (prd_type_id) DIMENSION BY (month, year) MEASURES (amount sales_amount) RULES UPDATE (sales_amount[FOR month FROM 1 TO 3 INCREMENT 1, 2004] =  ROUND(sales_amount[CURRENTV(), 2003] * 1.25, 2)) ORDER BY prd_type_id, year, month; 

Because cells for 2004 don t exist and RULES UPDATE is specified, no new rows are created in the array for 2004 ”and therefore the query doesn t return rows for 2004. The following output shows the output for the query; notice there are no rows for 2004:

 PRD_TYPE_ID   YEAR   MONTH SALES_AMOUNT ----------- ---------- ---------- ------------     1   2003   1  10034.84     1   2003   2  15144.65      1  2003     3  20137.83     1   2003    4  25057.45     1   2003    5  17214.56     1   2003    6  15564.64     1   2003   7 12654.84     1   2003    8  17434.82    1 2003   9  19854.57      1  2003   10   21754.19     1   2003    11 13029.73    1   2003    12 10034.84     2   2003   1   1034.84    2   2003    2   1544.65    2   2003   3   2037.83     2   2003   4  2557.45    2   2003    5   1714.56    2  2003    6 1564.64    2  2003   7  1264.84      2   2003    8  1734.82     2  2003    9  1854.57     2   2003   10  2754.19     2   2003   11  1329.73     2  2003   12  1034.84