Home » SQL & PL/SQL » SQL & PL/SQL » Evaluate a dynamic math expression in SQL (Oracle 10G database)
Evaluate a dynamic math expression in SQL [message #328726] Sun, 22 June 2008 07:50 Go to next message
satya.vanukuri
Messages: 2
Registered: June 2008
Junior Member
I have a user interface where the user can enter a formula using a set of parameters and operators. For eg , the user is given a list of parameters, say : Width, Height , Gauge. The list of operators include standard math functions : + - / * ^

He can select and create a formula like gauge * width. This is stored in a table. At a later time a job is scheduled to evaluate the formula based on parameters for width, gauge and height.

I can evaluate the expression by substituting each parameter.
I can do this using dynamic sql. However I'm not sure how to evaluate the ^ symbol. The equivalent of this is power function.

So if a user enters (height * 2) + (width^2) . How do i evaluate this. I am doing this in pl/sql.

Any ideas, please help.

thanks
satya
Re: Evaluate a dynamic math expression in SQL [message #328727 is a reply to message #328726] Sun, 22 June 2008 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
POWER

Regards
Michel
Re: Evaluate a dynamic math expression in SQL [message #328729 is a reply to message #328727] Sun, 22 June 2008 08:07 Go to previous messageGo to next message
satya.vanukuri
Messages: 2
Registered: June 2008
Junior Member
I am aware it is the power function. My question is if hte user enters a formula like

((0.25*width)+((gauge+height)^0.52)), I need to convert this to

((0.25*width)+(power(gauge+height),0.52)) since the symbol ^ doesn't mean anything in sql. how do i do this.

And ofcourse the ^ can repea any number of times. How do i do this.

thanks,

[Updated on: Sun, 22 June 2008 08:08]

Report message to a moderator

Re: Evaluate a dynamic math expression in SQL [message #328730 is a reply to message #328729] Sun, 22 June 2008 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Analyze the string and convert it.
what do you expect?
Of course, it is easier and more efficient if this is the inferface that does it.

Regards
Michel
Re: Evaluate a dynamic math expression in SQL [message #472306 is a reply to message #328730] Thu, 19 August 2010 16:04 Go to previous messageGo to next message
selfnick
Messages: 1
Registered: August 2010
Junior Member
I've got it.

You can execute an SQL like this, isn't it?

SQL> select 4*5 from dual;
20

Let's call '4*5' "the expression".

And you can execute dynamic SQL cursors, right?

The solution it's create a function with a varchar parameter called expression and a varchar return.

In that function, you will create a dynamic cursor like this:

'select ' || expression || ' from dual'

SQL> select my_eval_function('5*2') from dual;
10

Easy, isn't it?


Best wishes,


Mario Fernando S.
Colombia
Re: Evaluate a dynamic math expression in SQL [message #472313 is a reply to message #472306] Thu, 19 August 2010 16:51 Go to previous messageGo to next message
cookiemonster
Messages: 13952
Registered: September 2008
Location: Rainy Manchester
Senior Member
While the help is appreciated can you:
1) Read the orafaq forum guide
2) Read the question more carefully - the OP knows how to do what you describe, what he's asking is how to substitute the power function for ^
Re: Evaluate a dynamic math expression in SQL [message #472386 is a reply to message #328729] Fri, 20 August 2010 05:28 Go to previous message
_jum
Messages: 577
Registered: February 2008
Senior Member
regexp could help, as simple example use:
WITH data AS (SELECT '3.4+(gauge+height)^0.52-17.6' expr FROM dual)
  SELECT expr, 
         regexp_replace(expr,'\((.+)\)\^([0-9\.]*)(.*)','power(\1,\2)\3') nexpr
    FROM data;

3.4+(gauge+height)^0.52-17.6
3.4+power(gauge+height,0.52)-17.6                                                                       
Previous Topic: Error(7,29): PLS-00103
Next Topic: use decode function
Goto Forum:
  


Current Time: Sat Nov 09 21:30:10 CST 2024