Re: Select Statement

On May 21, 1:07 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
> <ame..._at_iwc.net> schreef in berichtnews:56fb119d-1b72-4825-863c-e829f48dce85_at_i36g2000prf.googlegroups.com...
>
>
>
>
>
> > Hi,
>
> > We have a procedure where we store the SELECT statements in a table,
> > and the procedure grabs them from the table and appends the WHERE
> > clause.....etc.....
>
> > In a few of the SELECTS we have formulas: A + B, A + B - C, A + B -
> > C / D.......
>
> > Sometimes the values that come in are legit, this is no problem.
> > Other times they come in a -99999.
>
> > What they want is that for a given formula (A + B), if ANY of the
> > values are -99999, then replace that value with a zero and perform the
> > formula. Easy, you can use DECODE.
>
> > However, if ALL the values in the formula are -99999, then leave the
> > result of the formula as -99999.
>
> > How can this be done? I can use a VERY cryptic DECODE statement.
> > But because the number of arguments in the formula can vary.....I do
> > not know if I can create a function to be called to process the values
> > passed, such as the value and the operator......this would be passing
> > arrays from a SQL statement......
>
> > Can this be done????
>
> > Thank you!
>
> My thoughts:
> don't put your SQL in a table, create a package with functions that do the
> calculations. To append the where clause, you might have to use dynamic sql
> though.
>
> Shakespeare

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.

Pain in the ass, but this is what they want........... Received on Wed May 21 2008 - 07:40:06 CDT