RE: SQL Question.

["Lim Hock-Chai" <Lim.Hock-Chai@xxxxxxxxxxxxxxx>]

Not sure why that wouldn't work.  Would below work? 
Select SMONTH,
       Cast(Sum(ONTSHP) AS Dec(7,0)) As TONT,           
       Cast(Sum(TOTSHP) As Dec(7,0)) As TSHP,
       Cast(AVG(ONTSHP/TOTSHP) As Dec(7,2)) As ON_TIME_P
  From LEADCSHP
  Where SCUST Not In(70500,89900,70600)
  Group By SMONTH
  Order by SMONTH  

-----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               

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.