Re: Need assistance with SQL statement over QSYS2.SYSTABLESTAT

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