Search notes:
Oracle SQL: MODEL clause
Test data
drop table tq84_model_clause;
create table tq84_model_clause (
row_ number,
col_1 number,
col_2 number,
col_3 number,
col_4 number,
col_5 number
);
insert into tq84_model_clause values (1, 14, 2, 3, 4, 5);
insert into tq84_model_clause values (2, 3, 8, 7, 22, 2);
insert into tq84_model_clause values (3, 2, 4, 11, 5, 5);
insert into tq84_model_clause values (4, 9, 13, 3, 8, 1);
insert into tq84_model_clause values (5, 10, 2, 0, null, 3);
insert into tq84_model_clause values (6, 1, 2, 3, 4, 5);
Minimal select statement with model clause
In order to use the model clause, the columns of a select statement must be assigned to dimensions , measures and, optionally, to partitions .
The columns that are assigned to dimensions will be used to identify the rows where values of the columns assigned to measures can be updated.
If
partitions are used, the rules formulated in an
SQL statement with a
model clause will act independently from each other on each partition.
The following select statement does not bother to use the partition clause and assigns one dimension only: row_.
Because no rules are defined, the values from the select statement won't be modified:
select
*
from
tq84_model_clause
model
dimension by ( row_ )
measures ( col_1, col_2, col_3, col_4, col_5 )
(
/* rules go here */
)
;
Update a column
Update all values in col_3 (that is where row_ has any value):
select
*
from
tq84_model_clause
model
dimension by ( row_ )
measures ( col_1, col_2, col_3, col_4, col_5 )
(
col_3[any] = 42
)
;
Add a condition
Update only values where row_ > 3:
select
*
from
tq84_model_clause
model
dimension by ( row_ )
measures ( col_1, col_2, col_3, col_4, col_5 )
(
col_3[row_ > 3] = 42
)
;
Add a record to the result set
Referencing a combination of dimension-values that does not yet exist creates this dimension.
There is no record with row_ = 7 in the test data, therefore, such a row will be created:
select
*
from
tq84_model_clause
model
dimension by ( row_ )
measures ( col_1, col_2, col_3, col_4, col_5 )
(
col_1[ 7 ] = 101,
col_2[ 7 ] = 102,
col_3[ 7 ] = 103
)
;
Use dimension value
CV(dimension-column) evaluates to the value of the current dimension of dimension-column
select
*
from
tq84_model_clause
model
dimension by ( row_ )
measures ( col_1, col_2, col_3, col_4, col_5 )
(
col_3[any] = cv(row_) * 111
)
;
Update value with value from «previous» record
Update col_3 with value from «previous» col_2:
select
*
from
tq84_model_clause
model
dimension by ( row_ )
measures ( col_1, col_2, col_3, col_4, col_5 )
(
col_3[any] = nvl(col_2[cv()-1], -9999)
)
;
Error messages
ORA-32611: incorrect use of MODEL CV operator
select
*
from
tq84_model_clause
model
dimension by ( row_ )
measures ( col_1, col_2, col_3, col_4, col_5 )
(
col_3[cv() > 3] = 42
)
;
ORA-32613: not a MODEL cell
select
*
from
tq84_model_clause
model
dimension by ( row_ )
measures (col_1, col_2, col_3, col_4, col_5 )
(
col_1 = col_1 * 2
)
;
ORA-32622: illegal multi-cell reference
select
*
from
tq84_model_clause
model
dimension by ( row_ )
measures ( col_1, col_2, col_3, col_4, col_5 )
(
col_1[row_] = col_2[row_] * col_3[row_]
)
;
ORA-00984: column not allowed here
select
*
from
tq84_model_clause
model
dimension by ( row_ )
measures ( col_1, col_2, col_3, col_4, col_5 )
(
col_1[row_] = col_1 * 2
)
;
Misc
Model functions
Model functions can only be used in the model clause . They include
cv cv can only be used on the right-hand side of a model rule. The function returns the current value of a dimension column or partitioning column (carried from the left-hand side). If used outside of a cell reference, an argument (dimension_column) is required. iteration_number Can only be used if iterate(num) is used. The function returns the number of completed iterations through the model rules (0 during the first iteration). presentnnv Can only be used on the right-nand side of a model rule. Compare with nvl2 presentv Can only be used on the right-nand side of a model rule. previous Can only be used in the iterate … [until] clause of the model rules clause (see also the ORA-32618: incorrect use of MODEL PREVIOUS function message).
ITERATE
The iterate clause executes the rules a number of times. The value of iteration_number starts with 0 and increases by 1 in each iteration.
select
id,
num
from
dual
model
dimension by (0 as id )
measures (0 as num)
rules
iterate (100)
(
num[iteration_number] = iteration_number * iteration_number
);
--
-- ID NUM
-- ---------- ----------
-- 0 0
-- 1 1
-- 2 4
-- …
-- 98 9604
-- 99 9801
--
-- 100 rows selected.
See also
Using the model clause in a
view prevents the view from being
updatable .