RE: Need assistance with SQL statement over QSYS2.SYSTABLESTAT

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

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Robert Clay
Sent: Monday, September 16, 2013 12:06 PM
To: midrange-l@xxxxxxxxxxxx; midrange-l@xxxxxxxxxxxx
Subject: Re: Need assistance with SQL statement over QSYS2.SYSTABLESTAT

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