# Re: Select Statement

Date: Thu, 22 May 2008 09:53:49 +0200

Message-ID: <48352697$0$14348$e4fe514c@news.xs4all.nl>

<kooroorinya_at_gmail.com> schreef in bericht
news: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 Received on Thu May 22 2008 - 02:53:49 CDT