# Re: Select Statement

Date: Wed, 21 May 2008 15:18:02 +0000 (UTC)

Message-ID: <g11efa$g4m$1@reader2.panix.com>

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."Received on Wed May 21 2008 - 10:18:02 CDT