Re: Select Statement

From: <>
Date: Wed, 21 May 2008 19:38:00 -0700 (PDT)
Message-ID: <>

On May 21, 7:38 am, "" <> 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!

If -99999 is the lowest possible value, you may be able to use the following construct

select case

         when greatest(a,b,c)=-99999 then -99999
         when least(a,b,c)=-99999 then 0
         else a+b-c
       end calc

 from table Received on Wed May 21 2008 - 21:38:00 CDT

Original text of this message