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 Go to next message
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 #445551 is a reply to message #445541] Tue, 02 March 2010 07:45 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What are you trying to achieve?
If your query does not provide you the correct result it is totally useless for us to know what it should return and what you want.

Post the result you want with the data you provided and explain the rules to reach it.

Regards
Michel
Re: model-clause with interdependend parameters [message #445553 is a reply to message #445551] Tue, 02 March 2010 07:49 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Dear Michel, thank you very much for your answer. You can find the correct results in the table temp_res, the formula is:

A = B(previous value) + 1
B = A(previous value) + 1

Does this help?
Re: model-clause with interdependend parameters [message #445556 is a reply to message #445553] Tue, 02 March 2010 08:08 Go to previous messageGo to next message
_jum
Messages: 508
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
Re: model-clause with interdependend parameters [message #445558 is a reply to message #445556] Tue, 02 March 2010 08:16 Go to previous message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Thank you very much, so AUTOMATIC ORDER makes the difference.
Previous Topic: Join Tables
Next Topic: update block issue
Goto Forum:
  


Current Time: Sun Sep 25 06:08:56 CDT 2016

Total time taken to generate the page: 0.10847 seconds