RE: SQL Question.

Hi Alan,

Your intermediate results are getting truncated....  There are a few quirks 
sometimes with precision.  One technique that always fixes things up for me is 
to always cast your intermediate results (like sum()) to a decimal type of the 
appropriate dimensions.  This makes your statement more cumbersum, but at least 
you get the correct results...  Also note that when multiplying by 100, I find 
it works better sometimes to explicitely add the .00000 to the value so that 
SQL doesn't think that it's an integer and chop off your fractions...

Select SMONTH,
       Dec(Sum(ONTSHP),7,0) As TONT,           
       Dec(Sum(TOTSHP),7,0) As TSHP,
       Dec(Dec(Sum(ONTSHP),7,0) / Dec(Sum(TOTSHP),7,0) * 100.0000 ,7,2) As 
ON_TIME_P 
  From LEADCSHP
  Where SCUST Not In(70500,89900,70600)
  Group By SMONTH
  Order by SMONTH  

hth,

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Alan Campin
Sent: Wednesday, November 08, 2006 12:25 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQL Question.


Anybody got any idea why this does not work? Why does calculation always
produce zero? Am I losing it here?

Problem has nothing to do with Cast as I can try it with and without and
same result. 

Anyway, thanks. 

Select SMONTH,
       Cast(Sum(ONTSHP) AS Dec(7,0)) As TONT,           
       Cast(Sum(TOTSHP) As Dec(7,0)) As TSHP,
       Cast(Sum(ONTSHP) / Sum(TOTSHP) As Dec(7,2)) As ON_TIME_P 
  From LEADCSHP
  Where SCUST Not In(70500,89900,70600)
  Group By SMONTH
  Order by SMONTH  

Month        TONT        TSHP   ON_TIME_P      
     1       1,408       1,782         .00      
     2       1,660       1,686         .00      
     3       1,336       1,376         .00      
     4       2,242       2,266         .00      
     5       2,020       2,052         .00      
     6       2,188       2,226         .00      
     7       1,878       1,908         .00      
     8       1,960       1,972         .00      
     9       2,018       2,022         .00      
    10       2,060       2,074         .00      
    11       2,052       2,072         .00      
    12         931         942         .00      
    13       1,039       1,056         .00