You Asked
Hi? this my query
I have a dictionary table
create table D_CONFIG_TST
(
row_name VARCHAR2(1500), -- text field with name of row
row_number VARCHAR2(50), -- text field with row number for order
row_array VARCHAR2(1500) -- text feild with array of row number, for example ('1,2,3,4')
)
And table with data
create table D_223_261
(
p_report VARCHAR2(150),
p_row_number VARCHAR2(220),
p_1 NUMBER(19,3),
p_2 NUMBER(19,3),
p_3 NUMBER(19,3),
p_4 NUMBER(19,3),
p_5 NUMBER(19,3),
p_6 NUMBER(19,3),
p_7 NUMBER(19,3),
p_8 NUMBER(19,3),
p_9 NUMBER(19,3),
p_10 NUMBER(19,3),
p_11 NUMBER(19,3),
p_12 NUMBER(19,3),
p_13 NUMBER(19,3),
p_14 NUMBER(19,3),
p_15 NUMBER(19,3),
p_16 NUMBER(19,3),
p_17 NUMBER(19,3),
p_18 NUMBER(19,3),
p_19 NUMBER(19,3),
p_20 NUMBER(19,3),
p_21 NUMBER(19,3),
p_22 NUMBER(19,3),
p_23 NUMBER(19,3),
p_24 NUMBER(19,3),
p_25 NUMBER(19,3),
p_26 NUMBER(19,3),
p_27 NUMBER(19,3),
p_28 NUMBER(19,3),
p_29 NUMBER(19,3),
p_30 NUMBER(19,3),
p_31 NUMBER(19,3),
p_32 NUMBER(19,3),
p_33 NUMBER(19,3),
p_34 NUMBER(19,3),
p_35 NUMBER(19,3),
p_36 NUMBER(19,3)
)
Then I`m aggregated data from table D_223_261. But, i need rows subtotals.
For example:
D_CONFIG_TST
row_name row_number row_array
TEST1 500 ('1,2,3')
TEST2 501 ('4,5,6')
D_223_261
P_report P_ROW_NUMBER P_1 P_2 P_3 P_4 name_row
134212 1 1 0 0 1 name1
134212 2 1 0 0 1 name2
134212 3 1 1 1 0 name3
134213 1 1 1 0 1 name1
134213 2 0 0 1 1 name2
134213 3 0 0 1 1 name3
134212 4 1 1 1 0 name1
134212 5 1 0 1 0 name2
134212 6 1 1 1 1 name3
134213 4 0 1 0 1 name1
134213 5 0 0 0 1 name2
134213 6 0 0 0 1 name3
Aggregate data
select p_row_number,sum(p_1+p_2) pp, sum(p_3+p_4)pp2 from d_223_261 group by p_row_number
Result that I wonted:
Into model rules
get row_name, row_number and row_array using subquery
select
p_row_number,
sum(p_1+p_2) pp,
sum(p_3+p_4)pp2
from d_223_261
group by p_row_number
model dimension by(p_row_number)
measures(pp,pp2)
rules(
pp[for (p_row_number,name_row) in (select distinct row_name,row_array, row_number from d_config_tst)] =
sum(pp)[p_row_number in (select cv(row_array) from d_config_tst)]
)
But I get a error, that subquery is not using for this statament (sum(pp)[p_row_number in (select cv(row_array) from d_config_tst)])
ROW_ARRAY is string value, and into IN CLAUSE don`t work, if I write
sum(pp)[p_row_number in ('1','2','3')] it`s work fine, but if I write sum(pp)[p_row_number in (cv(row_array))] sum is not calculate
and Chris said...
I don't really understand what you're trying to do here. But there are a few problems with what you're trying to do:
- You can't have subqueries in the model rules expression (the right hand side of the assignment)
- d_config_tst.row_array is handled as the literal value "1, 2, 3", i.e. all one string. Not an array of values "1", "2", "3".
For more about this problem, read:
http://asktom.oracle.com/Misc/varying-in-lists.html To get around these, do the following:
- Change d_config_tst so the array values are rows
- Outer join this do your other table
- Calculate the min and max values for an array in your model
- Change:
sum(pp)[p_row_number in ('1','2','3')]
to
sum(pp)[p_row_number between arrmin and arrmax]
Put it all together to give something like:
create table t1 (
v int
);
create table t2 (
val int,
p1 int
);
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (3);
insert into t2 values (1, 1);
insert into t2 values (2, 2);
insert into t2 values (3, 3);
insert into t2 values (4, 4);
insert into t2 values (5, 5);
select *
from t2
left join t1
on val = v
model
dimension by ( val )
measures ( p1, v, 0 as pp, 0 as vmin, 0 as vmax )
rules (
vmin[any] = min(v)[any],
vmax[any] = max(v)[any],
pp[for val in (select v from t1)] =
sum(p1)[val between vmin[cv()] and vmax[cv()]]
);
VAL P1 V PP VMIN VMAX
1 1 1 6 1 3
2 2 2 6 1 3
3 3 3 6 1 3
5 5 0 1 3
4 4 0 1 3
This assumes that your arrays are continuous values. For example, 1,2,3. Not 1, 3, 5.
Rating
(3 ratings)
Is this answer out of date? If it is, please let us know via a Comment