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)/C
OALESCE((D.dropCount + S.stockCount),D.dropCount,S.stockCount,0)) AS 'Averag
e_Sale'
FROM tableS S
,tableD D
WHERE blah...
Is there any way to handle that third calculated field more simply than to r
epeat 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 calulat
ed fields.
I'd like to keep things on-the-fly in the query instead of resorting to addi
tional programming.
Thanks!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:
>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 'To
tal_Sales'
> ,(COALESCE((D.dropSales + S.stockSales),D.dropSales,S.stockSales,0)/COALE
SCE((D.dropCount + S.stockCount),D.dropCount,S.stockCount,0)) AS 'Average_Sa
le'
> 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
>---
>
>
