oracle model类型,Oracle SQL高级编程——Model子句全解析-Oracle

下面的语句会报错，因既没有显式的指定求解顺序，又没有加Automatic Order，此时Oracle会使用Sequential Order这种顺序，并不适合这个规则。

SH@ prod> select product , country , year , week , inventory , sale , receipts from sales_fact

2 where country in (‘Australia’) and product = ‘Xtend Memory’ and week < 10

3 model return updated rows

4 partition by (product , country)

5 dimension by (year , week)

6 measures (0 inventory , sale , receipts)

7 rules (

8 inventory[year , week] = nvl(inventory[cv(year) , cv(week)-1] , 0 )

9 – sale[cv(year) , cv(week)]

10 + receipts[cv(year) , cv(week)])

11 order by product , country , year , week ;

select product , country , year , week , inventory , sale , receipts from sales_fact

*

ERROR at line 1:

ORA-32637: Self cyclic rule in sequential order MODEL

添加了Sequential Order之后，与上面的错误是一样的

SH@ prod> select product , country , year , week , inventory , sale , receipts from sales_fact

2 where country in (‘Australia’) and product = ‘Xtend Memory’ and week < 10

3 model return updated rows

4 partition by (product , country)

5 dimension by (year , week)

6 measures (0 inventory , sale , receipts)

7 rules sequential order (

8 inventory[year , week] = nvl(inventory[cv(year) , cv(week)-1] , 0 )

9 – sale[cv(year) , cv(week)]

10 + receipts[cv(year) , cv(week)])

11 order by product , country , year , week ;

select product , country , year , week , inventory , sale , receipts from sales_fact

*

ERROR at line 1:

ORA-32637: Self cyclic rule in sequential order MODEL

解决办法1，添另Automatic Order：

SH@ prod> select product , country , year , week , inventory , sale , receipts from sales_fact

2 where country in (‘Australia’) and product = ‘Xtend Memory’ and week < 10

3 model return updated rows

4 partition by (product , country)

5 dimension by (year , week)

6 measures (0 inventory , sale , receipts)

7 rules automatic order (

8 inventory[year , week] = nvl(inventory[cv(year) , cv(week)-1] , 0 )

9 – sale[cv(year) , cv(week)]

10 + receipts[cv(year) , cv(week)])

11 order by product , country , year , week ;

PRODUCT COUNTRY YEAR WEEK INVENTORY SALE RECEIPTS

—————————— ———- —– —- ———- ——- ———-

Xtend Memory Australia 1998 1 8.88 58 67.03

Xtend Memory Australia 1998 2 14.758 29 35.268

Xtend Memory Australia 1998 3 20.656 29 35.388

Xtend Memory Australia 1998 4 8.86 29 17.694

Xtend Memory Australia 1998 5 14.82 30 35.76

Xtend Memory Australia 1998 6 8.942 59 52.902

Xtend Memory Australia 1998 9 2.939 59 61.719

Xtend Memory Australia 1999 1 2.676 54 56.196

Xtend Memory Australia 1999 3 4.73 95 99.33

Xtend Memory Australia 1999 4 4.73 41 40.5

Xtend Memory Australia 1999 5 10.064 80 85.344

Xtend Memory Australia 1999 6 6.014 41 36.45

Xtend Memory Australia 1999 8 -2.196 103 100.914

Xtend Memory Australia 1999 9 13.806 53 69.342

Xtend Memory Australia 2000 1 -11.675 47 35.025

Xtend Memory Australia 2000 3 11.602 93 105.012

Xtend Memory Australia 2000 4 6.948 47 41.886

Xtend Memory Australia 2000 5 16.288 47 56.04

Xtend Memory Australia 2000 7 0 71 70.8

Xtend Memory Australia 2000 8 2.327 47 48.867

Xtend Memory Australia 2001 1 4.634 92 96.894

Xtend Memory Australia 2001 2 21.182 118 134.928

Xtend Memory Australia 2001 3 35.354 47 61.412

Xtend Memory Australia 2001 4 24.294 257 245.64

Xtend Memory Australia 2001 5 26.76 93 95.906

Xtend Memory Australia 2001 6 24.516 22 20.196

Xtend Memory Australia 2001 7 17.52 70 62.964

Xtend Memory Australia 2001 8 19.646 46 48.186

Xtend Memory Australia 2001 9 21.984 93 95.008

解决办法2，具体指定顺序：

SH@ prod> select product , country , year , week , inventory , sale , receipts from sales_fact

2 where country in (‘Australia’) and product = ‘Xtend Memory’ and week < 10

3 model return updated rows

4 partition by (product , country)

5 dimension by (year , week)

6 measures (0 inventory , sale , receipts)

7 rules (

8 inventory[year , week] order by year , week

9 = nvl(inventory[cv(year) , cv(week)-1] , 0 )

10 – sale[cv(year) , cv(week)]

11 + receipts[cv(year) , cv(week)])

12 order by product , country , year , week ;

PRODUCT COUNTRY YEAR WEEK INVENTORY SALE RECEIPTS

—————————— ———- —– —- ———- ——- ———-

Xtend Memory Australia 1998 1 8.88 58 67.03

Xtend Memory Australia 1998 2 14.758 29 35.268

Xtend Memory Australia 1998 3 20.656 29 35.388

Xtend Memory Australia 1998 4 8.86 29 17.694

Xtend Memory Australia 1998 5 14.82 30 35.76

Xtend Memory Australia 1998 6 8.942 59 52.902

Xtend Memory Australia 1998 9 2.939 59 61.719

Xtend Memory Australia 1999 1 2.676 54 56.196

Xtend Memory Australia 1999 3 4.73 95 99.33

Xtend Memory Australia 1999 4 4.73 41 40.5

Xtend Memory Australia 1999 5 10.064 80 85.344

Xtend Memory Australia 1999 6 6.014 41 36.45

Xtend Memory Australia 1999 8 -2.196 103 100.914

Xtend Memory Australia 1999 9 13.806 53 69.342

Xtend Memory Australia 2000 1 -11.675 47 35.025

Xtend Memory Australia 2000 3 11.602 93 105.012

Xtend Memory Australia 2000 4 6.948 47 41.886

Xtend Memory Australia 2000 5 16.288 47 56.04

Xtend Memory Australia 2000 7 0 71 70.8

Xtend Memory Australia 2000 8 2.327 47 48.867

Xtend Memory Australia 2001 1 4.634 92 96.894

Xtend Memory Australia 2001 2 21.182 118 134.928

Xtend Memory Australia 2001 3 35.354 47 61.412

Xtend Memory Australia 2001 4 24.294 257 245.64

Xtend Memory Australia 2001 5 26.76 93 95.906

Xtend Memory Australia 2001 6 24.516 22 20.196

Xtend Memory Australia 2001 7 17.52 70 62.964

Xtend Memory Australia 2001 8 19.646 46 48.186

Xtend Memory Australia 2001 9 21.984 93 95.008