Re: Select Statement

amerar_at_iwc.net <amerar_at_iwc.net> wrote:

> Well, I appreciate everyone's advice. but, that is the way the
> application is designed. And, changing over 4000 PHP scripts is not
> an option right now. Eventually things will be re-written, but not
> now.

> A user function is the way to go. But, the number of parameters
> passed is not known. The formula can be simple, A + B, that is 2
> parameters, or A + B - C, that is 3 parameters, A + B - C / D * F that
> is 5 parameters......

> So, I have to write some function that accepts a variable number of
> parameters, for both the value and the operator. And, this has to be
> done from an SQL statement.

Create a type, like so:

create type number_array is table of number;

Then create a function to check the numbers:

create function foo(numbers number_array) return number_array...

The function iterates through the set of numbers and replaces the ones that are -99999 with 0, unless they are all -99999.

You can call this function from SQL. Of course you will need to figure out how to get the values from the returned array and use them in the formula - but there's no way around that. The approach I would try is passing the formula to the same or a different function as a string with bind variables, and getting the values in it that way.

Note that you need to define what happens if D in your last example there is -99999 - substituting 0 will not work. If you need to substitute 1 instead that will complicate matters.

-- 
_______________________________________________________________________
Dan Blum					         tool_at_panix.com	
"I wouldn't have believed it myself if I hadn't just made it up."