Home » SQL & PL/SQL » SQL & PL/SQL » Creating own Formulas
Creating own Formulas [message #338532] Tue, 05 August 2008 05:22 Go to next message
usmanqamar
Messages: 6
Registered: January 2007
Junior Member
Hi guys,
i have a table T1 as below
VAR, Col_X , Col_Y
V1 , 10 , 2
V2 , 25 , 33
.....
I have a function f1(var in varchar2) that returns the value of VAR after doing computation on Col_X & Col_Y.
i want to use this function to make runtime formulas.


I cannot store these values neither update as the table T1 is changing. The only thing I need to know is the Column VAR.
Suppose I may get value of the above variables as following.
V1=10
V2=2
V3=3

Now I want to create some formulae from above variables and store in a table but the only problem is this will be calculated runtime ..e.g
TABLE T2:
COL_FORMULA, COL_FORMULA_VALS
Formula1 , V1+V2(V3*V1-V2)*V2
Formula2 , Formula1 + V3(V2-V1)
Formula3 , Formula2+Formula1
…………………………………………………………………………
…………………………………………………………………………
…………………………………………………………………………
The above formulas are using exiting formulas as well.
The question is How could I save these formulas & execute them as the above needs to be calculated run time.
Thank You.
Regards
Usman
Re: Creating own Formulas [message #338536 is a reply to message #338532] Tue, 05 August 2008 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nice exercise to do in PL/SQL.
Good luck!

As a clue:
SQL> declare
  2    v1      number := 1;
  3    v2      number := 2;
  4    formula varchar2(20) := ':v1 + :v2';
  5    res     number;
  6  begin
  7    execute immediate 'begin :res := '||formula||'; end;' using out res, v1, v2;
  8    dbms_output.put_line ('res='||res);
  9  end;
 10  /
res=3

PL/SQL procedure successfully completed.

Regards
Michel

[Updated on: Tue, 05 August 2008 05:32]

Report message to a moderator

Re: Creating own Formulas [message #338542 is a reply to message #338536] Tue, 05 August 2008 05:38 Go to previous messageGo to next message
usmanqamar
Messages: 6
Registered: January 2007
Junior Member
Thanks Michel,
Its really a good exercise if look to the real problem, i have shortned my problem and posted on forum.
Any way thanks for the reply. Its good and this the way i am already trying to do.
If you get some time later, try to work over it and please let me know.

Cheers
Usman
Re: Creating own Formulas [message #338551 is a reply to message #338542] Tue, 05 August 2008 05:45 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want more you have to post a test case: create table and insert statements along with the result you want with these data.

Don't forget to read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Previous Topic: sysdate
Next Topic: sum (merged 4 now)
Goto Forum:
  


Current Time: Thu Dec 08 18:12:30 CST 2016

Total time taken to generate the page: 0.08786 seconds