Re: Need assistance with SQL statement over QSYS2.SYSTABLESTAT

[Robert Clay <zreclay@xxxxxxxxx>]

Thanks, Birgitta!

Unless someone else has input, I'll go with this:

  CAST(CASE
         WHEN ( Number_Deleted_Rows = 0 )                         THEN   0
         WHEN ( Number_Rows = 0 ) AND ( Number_Deleted_Rows > 0 ) THEN 
100.00
         ELSE ( 100.00 *  Number_Deleted_Rows / ( Number_Rows + 
Number_Deleted_Rows ) )
       END
       AS DECIMAL( 5 , 2 )
      ) AS Percent_Deleted

"Contrariwise, if it was so, it might be; and if it were so, it would 
be; but as it isn't, it ain't. That's logic."--Tweedledee

On 09/16/2013 6:56 PM, Birgitta Hauser wrote:
> Robert,
>
> This:  (100 *  Number_Deleted_Rows / ( Number_Rows + Number_Deleted_Rows ) )
> may NOT return the result you expect!
>
> 100 has no decimal positions and in this way it is considered as being
> integer, so the result will be an integer again with truncated numbers!
> For example: (100 * 1000 / 1000000) will return 0 (without decimal
> positions!),
> While (100.0 * 1000 / 1000000) will return 0.1
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"