You are here:

Oracle/Executing a formulae in a field

Advertisement


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

ANSWER: Good day Natraj

Here is an example:

-- Create First Record
 CREATE TABLE "ECEBI"."FIRST_RECORD"
  (   "RECORD_ID" NUMBER,
  "LENGTH" NUMBER,
  "BREADTH" 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,
  "BREADTH" 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,
BREADTH,
HEIGHT)
VALUES (1,10,20,30);

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

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

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

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

INSERT INTO SECOND_RECORD
(RECORD_ID,
LENGTH,
BREADTH,
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

Answer
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,
"BREADTH" 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  

Oracle

All Answers


Answers by Expert:


Ask Experts

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

©2016 About.com. All rights reserved.