Re: Select Statement
Date: Wed, 21 May 2008 08:07:25 +0200
Message-ID: <4833bc25$0$14360$e4fe514c@news.xs4all.nl>
<amerar_at_iwc.net> schreef in bericht
news: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 Received on Wed May 21 2008 - 01:07:25 CDT