## MODEL Clause

### FOR Loops

In this tutorial we continue exploring the RULES part of the MODEL clause with a focus on FOR loops. FOR loops allow you to take a MODEL clause like this

```select
key ,
flag
from
t
model
dimension by ( key )
measures     ( '.' as flag )
rules
(
flag[2] = 'Y' ,
flag[4] = 'Y' ,
flag[6] = 'Y'
)
order by
key
;
```
```   KEY FLAG
------ ----
1 .
2 Y
3 .
4 Y
5 .
6 Y
7 .
8 .
9 .
10 .
```

and rewrite it like this ...

```select
key ,
flag
from
t
model
dimension by ( key )
measures     ( '.' as flag )
rules
(
flag[ FOR KEY IN ( 2, 4, 6 ) ] = 'Y'
)
order by
key
;
```
```   KEY FLAG
------ ----
1 .
2 Y
3 .
4 Y
5 .
6 Y
7 .
8 .
9 .
10 .
```

or this

```select
key ,
flag
from
t
model
dimension by ( key )
measures     ( '.' as flag )
rules
(
flag[ FOR KEY FROM 2 TO 6 INCREMENT 2 ] = 'Y'
)
order by
key
;
```
```   KEY FLAG
------ ----
1 .
2 Y
3 .
4 Y
5 .
6 Y
7 .
8 .
9 .
10 .
```

or this.

```select key from t2;
```
```   KEY
------
2
4
6
```
```select
key ,
flag
from
t
model
dimension by ( key )
measures     ( '.' as flag )
rules
(
flag[ FOR KEY IN ( SELECT KEY FROM T2 ) ] = 'Y'
)
order by
key
;
```
```   KEY FLAG
------ ----
1 .
2 Y
3 .
4 Y
5 .
6 Y
7 .
8 .
9 .
10 .
```

With "FOR dimension LIKE ..." syntax you can combine strings with incrementing/decrementing values like this.

```select
key_2 ,
flag
from
t
model
dimension by ( key_2 )
measures     ( '.' as flag )
rules
(
flag[ FOR KEY_2 LIKE 'T-%' FROM 2 TO 6 INCREMENT 2 ] = 'Y'
)
order by
key_2
;
```
```KEY_2 FLAG
----- ----
T-1   .
T-10  .
T-2   Y
T-3   .
T-4   Y
T-5   .
T-6   Y
T-7   .
T-8   .
T-9   .
```

#### Multiple Dimensions

To specify dimension value pairs, triplets, etc. you can use a FOR loop like this.

```select
group_1 ,
group_2 ,
flag
from
t
model
dimension by ( group_1, group_2 )
measures     ( '.' as flag )
rules
(
flag
[ FOR ( GROUP_1, GROUP_2 ) IN
(
( 'A', 'a1' ) ,
( 'B', 'a2' ) ,
( 'C', 'a1' )
)
] = 'Y'
)
order by
group_1 ,
group_2
;
```
```GROUP_1    GROUP_2    FLAG
---------- ---------- ----
A          a1         Y
A          a2         .
A          a3         .
B          a1         .
B          a2         Y
B          a3         .
C          a1         Y
C          a2         .
a1         .
a2         .
```

A multi-dimension FOR loop with a subquery looks like this.

```select
group_1 ,
group_2 ,
num_val ,
flag
from
t
model
dimension by ( group_1, group_2 )
measures     ( num_val, '.' as flag )
rules
(
flag
[ FOR ( GROUP_1, GROUP_2 ) IN
( SELECT GROUP_1, GROUP_2 FROM T WHERE NUM_VAL IS NULL )
] = 'Y'
)
order by
group_1 ,
group_2
;
```
```GROUP_1    GROUP_2    NUM_VAL FLAG
---------- ---------- ------- ----
A          a1             100 .
A          a2             200 .
A          a3             300 .
B          a1                 Y
B          a2             300 .
B          a3             100 .
C          a1             100 .
C          a2                 Y
a1             200 .
a2             800 .
```

#### FOR versus IN

It is important to note that a dimension reference with a FOR loop like `FOR KEY IN ( 2, 4, 6 )` differs from one with an IN expression like `KEY IN ( 2, 4, 6 )`. This is explained further in the Missing Cells - UPSERT tutorial.

#### Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

• [url=http://www.sqlsnippets.com/en/topic-11683.html]SQL Snippets: MODEL Clause - FOR Loops[/url]