MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » September 2013

Re: Need assistance with SQL statement over QSYS2.SYSTABLESTAT



fixed

Thanks for the input. It's a work in progress and has been altered considerably since the initial post.

I've dropped the first CASE statement and another WHEN clause was added:

WHEN Number_Rows = Number_Deleted_Rows THEN 50.00

So, the current Percent_Deleted (subject to change, of course) is:

, CASE
WHEN ( Number_Rows = 0 ) AND ( Number_Deleted_Rows = 0 ) THEN 0
WHEN ( Number_Rows > 0 ) AND ( 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
WHEN ( Number_Rows > Number_Deleted_Rows )
THEN DEC( ( DEC( Number_Deleted_Rows , 32 , 5 )
/ DEC( Number_Rows , 32 , 5 ) )
* 100.00
, 5 , 2 )
WHEN ( Number_Deleted_Rows > Number_Rows )
THEN DEC( ( DEC( Number_Rows , 32 , 5 )
/ DEC( Number_Deleted_Rows , 32 , 5 ) )
* 100.00
, 5 , 2 )
END
AS Percent_Deleted

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 1:24 PM, rob@xxxxxxxxx wrote:
On your first CASE, what if the number of rows = 49 and the number of
deleted rows = 49?


Rob Berendt






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