11 Replies Latest reply: Aug 19, 2013 2:37 PM by Etbin

Calculation

Hi Gurus,

I have a requirement as below:

I have two tables as :

TABLE_ONE

-------------------------

 ID Operator Variable Value 1 ( A 10 2 / B 20 3 ) 4 * C 50 5 # 6 + 7 ( D 50 8 / E 100 9 ) 10 * F 70 11 # 12 + 13 ( G 80 14 / H 40 15 ) 16 * I 0.1 17 * J 100

and another table is

TABLE_TWO

---------------------------

 ID FACTOR_1 FACTOR_2 TOTAL 4 0.5 50 25 10 0.5 70 35 17 0.2 100 20

----------------------------------------------------------------------------------------------------------

The question is:

There will be two factors involved: factor_1 and factor_2  as shown in the second table.

in the first table, there are several components separated by '#'.

The value of the variable just above the # will be inserted into the factor_2 column in the second table.

The factor_1 column will be the calculation of rest  of the variables (Excluding the variable just above the '#').

E.G:

For the sample below:

 13 ( G 80 14 / H 40 15 ) 16 * I 0.1 17 * J 100

The ID will be the Last variable's id (Just above the #)

The FACTOR_2 will be 100 : Last variable's amount (Just above the #)

The Factor_1 will be the calculation of rest of the fields.

i.e (G/H)*I         =>       (80/40)*0.1 = 0.2

and Total would be factor_1 * factor_2.

So The table will look like

 ID FACTOR_1 FACTOR_2 TOTAL 17 0.2 100 20

Thanks & Regards,

BS.

• 1. Re: Calculation

Maybe (somehow by brute force)

with

table_one as

(select 1 id,'(' operator,'A' variable,10 Value from dual union all

select 2,'/','B',20 from dual union all

select 3,')',null,null from dual union all

select 4,'*','C',50 from dual union all

select 5,'#',null,null from dual union all

select 6,'+',null,null from dual union all

select 7,'(','D',50 from dual union all

select 8,'/','E',100 from dual union all

select 9,')',null,null from dual union all

select 10,'*','F',70 from dual union all

select 11,'#',null,null from dual union all

select 12,'+',null,null from dual union all

select 13,'(','G',80 from dual union all

select 14,'/','H',40 from dual union all

select 15,')',null,null from dual union all

select 16,'*','I',0.1 from dual union all

select 17,'*','J',100 from dual

)

select id,

factor_1,

factor_2,

factor_1 * factor_2 total

from (select id,

case when operator = '*' and lead(operator) over (partition by cnt order by id) = '*'

then '?'

else operator

end operator,

value,cnt,factor_2,

case when operator = '*' and lag(operator) over (partition by cnt order by id) = '*'

then factor_1 * lag(factor_2) over (partition by cnt order by id)

else factor_1

end factor_1

from (select id,operator,value,cnt,

case when factor_1 is null

then first_value(factor_1 ignore nulls) over (partition by cnt order by id)

else factor_1 end factor_1,

case when factor_2 is null

then first_value(factor_2 ignore nulls) over (partition by cnt order by id)

else factor_2 end factor_2

from (select id,operator,value,

case when operator = '/'

then lag(value) over (order by id) / value

end factor_1,

case when operator = '*' then

value

end factor_2,

count(case when operator = '#' then 1 end) over (order by id) cnt

from table_one

)

)

)

where operator = '*'

order by id

IDFACTOR_1FACTOR_2TOTAL
4.55025
10.57035
17.210020

Regards

Etbin

• 2. Re: Calculation

Here's my stab at it...

First I need a stored function to evaluate the constructed expression:

```create or replace function toNumber(aExpression in varchar2) return number deterministic
is
result    number;
begin
execute immediate '
begin
:result := ' || aExpression || ';
end;'
using    out    result;

return    result;
end;
/

```

```with    t
as    (
select 1 ID,    '(' Operator,     'A' Variable,     10 Value    from DUAL union all
select 2,    '/',        'B',        20        from DUAL union all
select 3,    ')',        '',        null        from DUAL union all
select 4,    '*',        'C',        50        from DUAL union all
select 5,    '#',        '',        null        from DUAL union all
select 6,    '+',        '',        null        from DUAL union all
select 7,    '(',        'D',        50        from DUAL union all
select 8,    '/',        'E',        100        from DUAL union all
select 9,    ')',        '',        null        from DUAL union all
select 10,    '*',        'F',        70        from DUAL union all
select 11,    '#',        '',        null        from DUAL union all
select 12,    '+',        '',        null        from DUAL union all
select 13,    '(',        'G',        80        from DUAL union all
select 14,    '/',        'H',        40        from DUAL union all
select 15,    ')',        '',        null        from DUAL union all
select 16,    '*',        'I',        0.1        from DUAL union all
select 17,    '*',        'J',        100        from DUAL union all
select 18,    '#',        '',        null        from DUAL
)
,    f2
as    (
select    f2.Id, f2.Operator, f2.Value
from    t
,    t f2
where    t.Operator = '#'
and    f2.Id = t.Id - 1
)
,    f1
as    (
select    f2.Id F2_Id, listagg(f1.Operator || f1.Value, '') within group (order by f1.Id) F1_Expression
from    f2
,    t f1
where    f1.Id between nvl((select max(x.Id) + 3 /* Skip the operator following #*/ from f2 x where x.Id < f2.Id), 1) and f2.Id - 1
group by
f2.Id
)
select    f2.Id
,    f2.Value F2_Value
,    f1.F1_Expression
,    toNumber(f1.F1_Expression) F1_Value
,    toNumber(f1.F1_Expression || f2.Operator || to_char(f2.Value)) Total
from    f2
join    f1 on F2_Id = f2.Id;

```

Which returns:

IDF2_VALUEF1_EXPRESSIONF1_VALUETOTAL

4

50

(10/20)

0.5

25

10

70

(50/100)

0.5

35

17

100

(80/40)*.1

0.2

20

HTH

Gerard

• 3. Re: Calculation

Sounds like you might be interested in Odie's PL/SQL RPN Calculator

http://odieweblog.wordpress.com/2013/02/03/plsql-rpn-calculator/

This is something I’ve developed recently to evaluate stored expressions (formulas) using variables.

The “calculator” is written in PL/SQL and implements an RPN evaluation technique, as well as a method to convert infix expressions to RPN using the shunting-yard algorithm.

The evaluation function does not involve any dynamic SQL.

Program and additional objects available here : rpn_util.zip

• 4. Re: Calculation

Hi Etbin,

suppose i have the same table with some additional columns as below and my expected result ias as shown.

 18 #
 19 + X 600
 20 #
 21 - Y 1100

And my result table would be

 19 1 600 600
 21 1 1100 1100

Thanks & Regards,

BS.

• 5. Re: Calculation

I think a calculation using an arbitrary formula cannot be handled with Analytic Functions.

It might be suitable for a group of particular cases when you don't want to use Dynamic SQL (Execute Immediate).

with

table_one as

(select 1 id,'(' operator,'A' variable,10 Value from dual union all

select 2,'/','B',20 from dual union all

select 3,')',null,null from dual union all

select 4,'*','C',50 from dual union all

select 5,'#',null,null from dual union all

select 6,'+',null,null from dual union all

select 7,'(','D',50 from dual union all

select 8,'/','E',100 from dual union all

select 9,')',null,null from dual union all

select 10,'*','F',70 from dual union all

select 11,'#',null,null from dual union all

select 12,'+',null,null from dual union all

select 13,'(','G',80 from dual union all

select 14,'/','H',40 from dual union all

select 15,')',null,null from dual union all

select 16,'*','I',0.1 from dual union all

select 17,'*','J',100 from dual union all

select 18,'#',null,null from dual union all

select 19,'+','X',600 from dual union all

select 20,'#',null,null from dual union all

select 21,'-','Y',1100 from dual

)

select id,

factor_1,

factor_2,

factor_1 * factor_2 total

from (select id,

case when operator = '*' and lead(operator) over (partition by cnt order by id) = '*'

then '?'

else operator

end operator,

value,cnt,factor_2,

case when operator = '*' and lag(operator) over (partition by cnt order by id) = '*'

then factor_1 * lag(factor_2) over (partition by cnt order by id)

when operator in ('+','-')

then to_number(operator || '1') /* must distinguish between the two signs */

else factor_1

end factor_1

from (select id,operator,value,cnt,

case when factor_1 is null

then first_value(factor_1 ignore nulls) over (partition by cnt order by id)

else factor_1 end factor_1,

case when factor_2 is null

then first_value(factor_2 ignore nulls) over (partition by cnt order by id)

else factor_2 end factor_2

from (select id,operator,value,

case when operator = '/'

then lag(value) over (order by id) / value

end factor_1,

case when operator in ('+','-','*')

then value

end factor_2,

count(case when operator = '#' then 1 end) over (order by id) cnt

from table_one

)

)

)

where operator in ('+','-','*')

and value is not null

order by id

Regards

Etbin

• 6. Re: Calculation

Hi Etbin,

I am confused again.

Let me explain this again...

 ID Operator Variable Value 1 ( 2 ( A 10 3 / B 20 4 ) 5 * C 50 6 * D 10 7 ) 8 # 9 + 10 ( D 50 11 / E 100 12 ) 13 * F 70 14 # 15 + 16 ( G 80 17 / H 40 18 ) 19 * I 0.1 20 * J 100 21 # 22 + X 600 23 # 24 - Y 1100

The variable just before the '#' will always have the amount factor associated with it.

and the result column is:

 ID FACTOR_1 FACTOR_2 TOTAL 5 1 50 50 6 25 10 250 13 0.5 70 35 19 1 0.1 0.1 20 2 100 20 22 1 600 600 24 1 1100 1100

Hope the table is much clear now.

I tried to put the statement in a procedure . But it disnt work for me

Thanks & Regardsn,

BS.

• 7. Re: Calculation

drop table t;

create table t(ID,Operator,Variable,Value) as select

1,'(','A',10 from dual union all select

2,'/','B',20 from dual union all select

3,')',null,null from dual union all select

4,'*','C',50 from dual union all select

5,'#',null,null from dual union all select

6,'+',null,null from dual union all select

7,'(','D',50 from dual union all select

8,'/','E',100 from dual union all select

9,')',null,null from dual union all select

10,'*','F',70 from dual union all select

11,'#',null,null from dual union all select

12,'+',null,null from dual union all select

13,'(','G',80 from dual union all select

14,'/','H',40 from dual union all select

15,')',null,null from dual union all select

16,'*','I',0.1 from dual union all select

17,'*','J',100 from dual;

select id, to_number(column_value) factor1, factor2, factor2*to_number(column_value) total

from (

select id_last_val id,

listagg(

case when id < id_last_val then

operator||to_char(value, 'tm', 'nls_numeric_characters=''.,''')

end

) within group(order by id) factor1,

last_val factor2

from (

select a.*,

first_value(value) over(partition by grp order by id desc) last_val,

first_value(id) over(partition by grp order by id desc) id_last_val

from (

select a.*,

last_value(case when operator = '#' then id end) ignore nulls over(order by id desc) grp

from t a

) a

where operator != '#'

)

group by grp, id_last_val, last_val

order by grp

), xmltable(replace(factor1,'/',' div '));

IDFACTOR1FACTOR2 TOTAL
4   0.5   50    25
10   0.5   70    35
17   0.2   100    20

Message was edited by: StewAshton

• 8. Re: Calculation

You really should refer people back to your previous thread(s) on this subject so they can see what you've already been asking and what solutions you've already been provided with:

Last and Rest Value in a string

Formula Value Creation

And then at least people will have a chance of not re-doing what has already been told to you before.

• 9. Re: Calculation

Dear Blu,

The questions i asked before didnt help me in finding out the exact solution .. so i had to post it again .. my apology

Thanks & Regards,

BS.

• 10. Re: Calculation

As already said: just for this formula structure (i.e. a division followed by one or two multiplications)

just a demo that it can be done

with

table_one as

(select 1 id,'(' operator,null variable,null Value from dual union all

select 2,'(','A',10 from dual union all

select 3,'/','B',20 from dual union all

select 4,')',null,null from dual union all

select 5,'*','C',50 from dual union all

select 6,'*','D',10 from dual union all

select 7,')',null,null from dual union all

select 8,'#',null,null from dual union all

select 9,'+',null,null from dual union all

select 10,'(','D',50 from dual union all

select 11,'/','E',100 from dual union all

select 12,')',null,null from dual union all

select 13,'*','F',70 from dual union all

select 14,'#',null,null from dual union all

select 15,'+',null,null from dual union all

select 16,'(','G',80 from dual union all

select 17,'/','H',40 from dual union all

select 18,')',null,null from dual union all

select 19,'*','I',0.1 from dual union all

select 20,'*','J',100 from dual union all

select 21,'#',null,null from dual union all

select 22,'+','X',600 from dual union all

select 23,'#',null,null from dual union all

select 24,'-','Y',1100 from dual

)

select id,factor_1,factor_2,factor_1 * factor_2 total

from (select id,operator,value,

case when operator = '*'

then case when lag(operator) over (order by id) = '*'

then lag(factor_1,2) over (order by id) * lag(factor_2) over (order by id)

when lead(operator) over (order by id) = '#'

then lag(factor_1) over (order by id)

else factor_1

end

else factor_1

end factor_1,

factor_2

from (select id,operator,value,

case when operator = '/'

and lag(operator) over (order by id) = '('

then lag(value) over (order by id) / value

when operator = '*'

then 1

when operator in ('+','-')

and lag(operator) over (order by id) = '#'

then to_number(operator || '1')

end factor_1,

case when operator = '*'

then value

when operator in ('+','-')

and lag(operator) over (order by id) = '#'

then value

end factor_2

from table_one

where value is not null

or operator = '#'

)

)

where operator in ('+','-','*')

order by id

IDFACTOR_1FACTOR_2TOTAL
515050
62510250
13.57035
191.1.1
20.210020
221600600
24-11100-1100

Regards

Etbin

• 11. Re: Calculation

If the formula is defined simply as showed (each # group:  (?/?) *V1*V2*...*Vi), it may be worth a try like below.  Otherwise, a varying formula is just abuse to SQL although the design looks nice.  In that case, it had better to reconstruct the expression for each factor and get the values.

below consider the case (?/?)*V1 with only one # group (for multiple groups set rownum rid for its own partition and assign each group a gid for recursive join)

In case (?/?) *V1*V2*...*Vi), the recursive sql should not be much different considering only V1 part is recursively processed (and finally remap factor1 in the returned multiple * rows in that group).

{code}

1  with tmp (rid, id, op, var, val, lvl, f1, f2, total, val1, val2) as

2  (select x.*, 1 lvl,

3  decode(x.operator, '*', 1, 1),

4  decode(x.operator, '*', x.value, 1),

5  0, 1, 1

6  from x where rid=1

7  union all

8  select x.rid, tmp.id, tmp.op, tmp.var, tmp.val, tmp.lvl+1,

9  tmp.f1,

10  tmp.f2,

11  null,

12  decode(x.operator, '(', x.value, tmp.val1),

13  decode(x.operator, '/', x.value, tmp.val2)

14  from tmp, x where x.rid=tmp.rid+1)

15  select

16  --*

17  id, val1/val2 f1, f2, (val1/val2)*f2 total

18  from tmp

19* where rid=id

SQL> /

ID         F1         F2      TOTAL

---------- ---------- ---------- ----------

4         .5         50         25

SQL> desc xt

Name

--------------------------------------------------------------------------------------

ID

OPERATOR

VARIABLE

VALUE

SQL> desc x

Name

--------------------------------------------------------------------------------------

RID

ID

OPERATOR

VARIABLE

VALUE

SQL> select * from x ;

RID         ID O V      VALUE

---------- ---------- - - ----------

1          4 * C         50

2          3 )

3          2 / B         20

4          1 ( A         10

{code}