You are here:

# Oracle/Executing a formulae in a field

Question
QUESTION: Dear Elliot,
we have a table containingfour  fields 1. length (L),2. breadth(B), 3. Height(H) and a 4. formula(F_Formula) where the words in the braces are field names .
we would like to execute the formula directly from the field Formulae for each of the record. the formula may have something like  3L*2H+B.

This means in the first record the field name formula will have (3L*2H+B) and the fields L , H & B has values 7,2,3 respectively /

The second record may contain (2L*5H*2B) in the formula and fields L,H, & B has values 3,6,2 .

so  we need to read these 2 or more records and execute the formula that is inside each record using the values of L,B,H in the same record.

the result should give the following for the above 2 records.

first record formula (3L*2H+B) i.e. 3*7*2*2+3 =  87
second record formula (2L*5H*2B) i.e. 2*4*5*6+2*2 = 244

In other words we should be able to get these 2 results by executing a select statement or a procedure or any other way,
we need this formulae to be executed for a project where some Bill of quantities are calculated based on some other formulae.

Best Regards
Natraj

Here is an example:

-- Create First Record
CREATE TABLE "ECEBI"."FIRST_RECORD"
(   "RECORD_ID" NUMBER,
"LENGTH" NUMBER,
"HEIGHT" NUMBER,
"CALCULATION" NUMBER GENERATED ALWAYS AS (3*LENGTH*2*HEIGHT+BREADTH)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING;

-- Create Second Record
CREATE TABLE "ECEBI"."SECOND_RECORD"
(   "RECORD_ID" NUMBER,
"LENGTH" NUMBER,
"HEIGHT" NUMBER,
"CALCULATION" NUMBER GENERATED ALWAYS AS (2*LENGTH*5*HEIGHT+2*BREADTH)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING;

-- Insert Records
-- INSERT
INSERT INTO FIRST_RECORD
(RECORD_ID,
LENGTH,
HEIGHT)
VALUES (1,10,20,30);

INSERT INTO FIRST_RECORD
(RECORD_ID,
LENGTH,
HEIGHT)
VALUES (2,50,60,70);

INSERT INTO FIRST_RECORD
(RECORD_ID,
LENGTH,
HEIGHT)
VALUES (3,7,2,3);

INSERT INTO SECOND_RECORD
(RECORD_ID,
LENGTH,
HEIGHT)
VALUES (1,10,20,30);

INSERT INTO SECOND_RECORD
(RECORD_ID,
LENGTH,
HEIGHT)
VALUES (2,50,60,70);

INSERT INTO SECOND_RECORD
(RECORD_ID,
LENGTH,
HEIGHT)
VALUES (3,4,6,2);

Now, you have many options to retrieve them in a single sql.

Option #1:  UNION ALL
select 'FIRST RECORD', calculation
from first_record
where record_id = 3
UNION ALL
select 'SECOND_RECORD', calculation
from second_record
where record_id = 3
;

'FIRSTRECORD' CALCULATION
------------- -----------
FIRST RECORD          87
SECOND_RECORD         244

Option #2:  Simple Join
SELECT first.record_id, first.calculation as first_record_calculation, second.calculation as second_record_calculation
from first_record first, second_record second
where first.record_id = second.record_id;

RECORD_ID FIRST_RECORD_CALCULATION SECOND_RECORD_CALCULATION
--------- ------------------------ -------------------------
1          1820          3040
2          21060          35120
3          87          244

Hope this helps, let me know if you have additional question

Elliot

---------- FOLLOW-UP ----------

QUESTION: Thanks Elliot , this is something new and is helpful a bit, however the only problem we will have is the formula is not fixed , this is entered by the user and can be anything I have given only the formula as an Example we will not know this and this will be based on what the user enteres,

Best Regards
Natraj

Hi Natraj

What I will suggest you is to do the following:
(1) Update the create script above to change the "CALCULATION" column be VARCHAR2(50).  This column will store the formula in which the user will enter

CREATE TABLE "ECEBI"."FIRST_RECORD"
( "RECORD_ID" NUMBER,
"LENGTH" NUMBER,
"HEIGHT" NUMBER,
"CALCULATION" VARCHAR2(50)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING;

(2) Come up with a rule to ensure that there is a format or rules that the user must entered
(3) Create a function, pass the "CALCULATION" value, parse the formula, perform the calculation
(4) use the function in the select statement to retrieve the calculated value.

Hope it helps.
Elliot
Questioner's Rating
 Rating(1-10) Knowledgeability = 9 Clarity of Response = 10 Politeness = 10 Comment Thanks Elliot... Will try this .. Regards Natraj

Oracle

Volunteer

#### Elliot Mak

##### Expertise

I can answer questions with regarding to Oracle DB (8i, 9i, 10g, 11g) installation, configuration, administration, Data Guard, SQL, and PL/SQL (Procedures, triggers, functions).

##### Experience

I am an Oracle DBA, Senior PA, Project Manager, and Data Architect with 10 years of experience.

Education/Credentials
Bachelor of Science in Management System