Re: Select Statement

On May 20, 4:38 pm, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> 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!

A case statement would allow you do to multiple compares and might prove more flexible than decode for you.

I am against storeing SQL in tables like this since it requires the use of dynamic SQL which generally is not as efficient as using prepared SQL and in many cases the there is not valid business reason to store the queries like this anyhow.

Also unless your process is carefully designed you could be creating an SQL injection vulunarbility.

Still consider the case statment and you might also consider writing user functions and passing the parameters to the function and letting it handle the logic and spit back the final calculated value.

Now that I thing about your problem description why arn't you using user functions?

HTH -- Mark D Powell -- Received on Tue May 20 2008 - 15:57:03 CDT