MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » September 2013

Re: Need assistance with SQL statement over QSYS2.SYSTABLESTAT



fixed

Scott,

You're right.

Combined with the other suggestions, how about this?

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

Might not even need the third WHEN.

Robert

"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 2:40 PM, Scott Mildenberger wrote:
Robert,

Your first two whens can be combined into one

WHEN Number_Deleted_Rows = 0 THEN 0

Also, your very last when doesn't return 'Percent deleted', consider 1 Row and 5 deleted. Returns 20%. The more deleted the smaller the percent, is that what you really want? You may be able to use the when before that as an ELSE catchall.


Scott Mildenberger
Programmer/Analyst
Davis Transport Inc.
Missoula, MT
406-728-5510 x128








Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact