I have a table with 3 fields:
Field 1 (NUMBER): have values like: 1, 2,50 ,40
Field 2 (VARCHAR): have values like: "+" , "-", "*"
Field 3 (NUMBER): like Field1: 5,65.4, 90
how i can evaluate with content of this field 2.
example
|field1| field2| field3| RESULT
-------------------------------
| 1| +| 5| 6
| 1| *| 5| 5
| 1| -| 5| -4
I don't know of any EVAL type functionality outside EXECUTE IMMEDIATE in PL/SQL, and that's limited to one statement at a time. You can't build something that will add for one row, multiply for another, and subtract for yet another, but you could create a cursor, loop through it, and build an EXECUTE IMMEDIATE to evaluate against the values on each cursor row. Definitely look into that if you're thinking of operations more complicated than simple math with two operands.
The query below will do what you asked, but if you're looking to do anything more complex than an operation between two columns it will get cumbersome pretty quickly:
SELECT
CASE field2
WHEN '+' THEN field1 + field3
WHEN '*' THEN field1 * field3
WHEN '-' THEN field1 - field3
END
FROM ...
As Annjawn explains in this answer you can use some of the built in functionality of XMLQuery, specifically .getnumberval(), to fake the formulae. This will be considerably slower than a CASE statement but it is a lot cleaner.
Assuming the table:
create table tmp_test (
field1 number not null
, field2 varchar2(1) not null
, field3 number not null );
insert all
into tmp_test values (1, '+', 5)
into tmp_test values (1, '*', 5)
into tmp_test values (1, '-', 5)
select * from dual;
Your query would look like this:
SQL> select xmlquery(field1 || field2 || field3 returning content
2 ).getnumberval() as field4
3 from tmp_test;
FIELD4
----------
6
5
-4
I would strongly advise against storing a calculated column in the database though. It will only cause problems as if you update the database your column does not get updated. The correct way to do this is to either have a view, that includes the definition of your calculation and always select from this view or to use a VIRTUAL column as defined in the create table documentation.
You can then either use XMLQuery or a DETERMINISTIC function, for instance the one provided by APC, to automatically calculate your column.
If your table is created as follows:
create table tmp_test (
field1 number not null
, field2 varchar2(1) not null
, field3 number not null
, field4 number generated always as (
xmlquery(field1 || field2 || field3 returning content
).getnumberval()
) virtual
);
Then a much simpler query will get your result
SQL> select * from tmp_test;
FIELD1 F FIELD3 FIELD4
---------- - ---------- ----------
1 + 5 6
1 * 5 5
1 - 5 -4
The other benefit of this is that your calculations are always done in exactly the same way. They are not implemented differently for each individual piece of code that needs this computed column
You could build an evaluation function
create or replace function eval
( n1 in number
, n2 in number
, op in varchar2 )
return number deterministic
as
result number;
begin
execute immediate ' select :1 '|| op || ' : 2 from dual'
into result
using n1, n2;
return result;
end;
/
You could then call it in a SELECT statement:
select eval ( field1, field3, field2) as result
from your_table;
This will cope with simple arithmetic but it is not a good approach for more complicated formulae. If you want to go down that route I suggest you follow the link in my answer to another question about doing mathematics in PL/SQL. Find out more.
SELECT
field1,
field2,
field3,
Case
When field2='+' then field1+field3
When field2='*' then field1*field3
When field2='-' then field3-field1
else 0
end as Result
FROM table
