Creating own Formulas [message #338532] |
Tue, 05 August 2008 05:22  |
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   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
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  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|