complex calculated fields

21 Dec 2005 8:14 PM

_n8

Does anyone have any shortcuts for doing complex calculated fields?

Example:

Code: -------------------- SELECT COALESCE((D.dropCount + S.stockCount),D.dropCount,S.stockCount,0) AS 'Total_Count'
,COALESCE((D.dropSales + S.stockSales),D.dropSales,S.stockSales,0) AS 'Total_Sales'
,(COALESCE((D.dropSales + S.stockSales),D.dropSales,S.stockSales,0)/COALESCE((D.dropCount + S.stockCount),D.dropCount,S.stockCount,0)) AS 'Average_Sale'
FROM tableS S
,tableD D
WHERE blah....
--------------------

Is there any way to handle that third calculated field more simply than to repeat the calculations/formulae for the previous two fields?

This is a pretty straightforward example of what I am up against, in my full query, I have calculated fields that are based on up to seven other calulated fields.

I'd like to keep things on-the-fly in the query instead of resorting to additional programming.

Thanks! -- _n8 ------------------------------------------------------------------------ Posted via http://www.codecomments.com ------------------------------------------------------------------------

10 Jan 2006 6:18 AM

Steve Kass

I think this makes a little progress:

SELECT
Total_Count,
Total_Sales,
Total_Count/Total_Sales AS Average_Sales
FROM (
SELECT
    COALESCE(D.dropCount,0) + COALESCE(S.stockCount),0) AS Total_Count,
    COALESCE(D.dropSales,0) + COALESCE(S.stockSales),0) AS Total_Sales
from tableS S, tableD D
where blah...
) t

If there are only two tables, you could write

select
D.dropCount + S.stockCount as ToTal_Count,
...
from (
select
    coalesce(dropCount,0) as dropCount,
    coalesce(dropSales,0) as dropSales,
     ...
) as D, (
    select
      ...
    from tableS
) as S

Or you could just use 0 in your data to mean zero, if NULL means
no more than that.

Steve Kass
Drew University

_n8 wrote:

Show quote

>Does anyone have any shortcuts for doing complex calculated fields?
>
>Example:
>
>
>Code:
>--------------------
> SELECT COALESCE((D.dropCount + S.stockCount),D.dropCount,S.stockCount,0) AS 'Total_Count'
> ,COALESCE((D.dropSales + S.stockSales),D.dropSales,S.stockSales,0) AS 'Total_Sales'
> ,(COALESCE((D.dropSales + S.stockSales),D.dropSales,S.stockSales,0)/COALESCE((D.dropCount + S.stockCount),D.dropCount,S.stockCount,0)) AS 'Average_Sale'
> FROM tableS S
> ,tableD D
> WHERE blah....
>
>--------------------
>
>
>Is there any way to handle that third calculated field more simply than
>to repeat the calculations/formulae for the previous two fields?
>
>This is a pretty straightforward example of what I am up against, in my
>full query, I have calculated fields that are based on up to seven other
>calulated fields.
>
>I'd like to keep things on-the-fly in the query instead of resorting to
>additional programming.
>
>Thanks!
>
>
>
>--
>_n8
>------------------------------------------------------------------------
>Posted via http://www.codecomments.com
>------------------------------------------------------------------------
>
>
>