Re: Select Statement

On May 22, 5:53 pm, "Shakespeare" <what..._at_xs4all.nl> wrote:
> <kooroori..._at_gmail.com> schreef in berichtnews:a6347c54-3e27-4931-a26e-278d518ec03a_at_w5g2000prd.googlegroups.com...
> On May 21, 7:38 am, "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!
>
> -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
>
> This does not meet the specs:
> -99999 + 3 -1 should calculate to 2, not 0 ..
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -

Your right, I misread the specs first time around. Then possibly could create a simple function to return 0 if -9999

create function x(i numeric) return number ... return if i=-99999 then 0 else i ...

select case

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

Don't have access to db today to do test, does still presumes that no number < -99999, of course, could create another fn to do that if needed, but where do you stop and decide it would be good to change the source data? Received on Thu May 22 2008 - 21:40:02 CDT