Home » SQL & PL/SQL » SQL & PL/SQL » model-clause with interdependend parameters (10g)
model-clause with interdependend parameters [message #445541] |
Tue, 02 March 2010 07:11  |
steffeli
Messages: 112 Registered: July 2006
|
Senior Member |
|
|
Dear all,
I want to calculate two parameters A and B which are interdependend. Unfortunately, my code is not working correctly:
create table temp_res1 as
select *
from temp_res
model
dimension by (rn)
measures ( 0 as A,
0 as B
)
rules ( A[rn=1] = 5,
B[rn=1] = 10,
A[rn>1] order by rn asc
= B[cv()-1] + 1,
B[rn>1] order by rn asc
= A[cv()-1] + 1
)
The correct result should be:
create table temp_res (rn integer, a integer, b integer);
insert into temp_res values(1, 5, 10);
insert into temp_res values(2, 11, 6);
insert into temp_res values(3, 7, 12);
insert into temp_res values(4, 13, 8);
insert into temp_res values(5, 9, 14);
insert into temp_res values(6, 15, 10);
insert into temp_res values(7, 11, 16);
insert into temp_res values(8, 17, 12);
but I get:
create table temp_res (rn integer, a integer, b integer);
insert into temp_res values(1, 5, 10);
insert into temp_res values(2, 11, 6);
insert into temp_res values(3, 1, 12);
insert into temp_res values(4, 1, 2);
insert into temp_res values(5, 1, 2);
insert into temp_res values(6, 1, 2);
insert into temp_res values(7, 1, 2);
insert into temp_res values(8, 1, 2);
Any idea why the code is not working correctly?
Thanks, Stefan
[Updated on: Tue, 02 March 2010 07:43] Report message to a moderator
|
|
|
|
|
Re: model-clause with interdependend parameters [message #445556 is a reply to message #445553] |
Tue, 02 March 2010 08:08   |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
USE AUTOMATIC ORDER
WITH data AS (SELECT level rn FROM dual CONNECT BY level<=8)
SELECT * FROM data
MODEL
DIMENSION BY (rn)
MEASURES
(0 as A,
0 as B)
RULES
AUTOMATIC ORDER
( A[1] = 5,
B[1] = 10,
A[rn>1] ORDER BY rn ASC = B[cv()-1] + 1,
B[rn>1] ORDER BY rn ASC = A[cv()-1] + 1
)
RN A B
----------------------
1 5 10
2 11 6
3 7 12
4 13 8
5 9 14
6 15 10
7 11 16
8 17 12
|
|
|
|
Goto Forum:
Current Time: Sat Feb 15 09:52:25 CST 2025
|