[OS v6.1.0, mostly current on PTFs. I apologize in advance if the
formatting doesn't work but Thunderbird sometimes does its own thing.]
Using the following SQL statement and a 'Table_Schema' value that I know
has the first four CASE WHEN clauses satisfied, I can see that the first
3 WHEN clauses work but the fourth one does not, returning a value of 0.00:
SELECT
Table_Schema
, Table_Name
, Number_Rows
, Number_Deleted_Rows
, CASE
WHEN ( Number_Rows = 0 ) AND ( Number_Deleted_Rows = 0 ) THEN '1'
WHEN ( Number_Rows > 0 ) AND ( Number_Deleted_Rows = 0 ) THEN '2'
WHEN ( Number_Rows = 0 ) AND ( Number_Deleted_Rows > 0 ) THEN '3'
WHEN ( Number_Rows > Number_Deleted_Rows ) THEN '4'
WHEN ( Number_Deleted_Rows > Number_Rows ) THEN '5'
END
AS Algorithm_Used
, DECIMAL( 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
WHEN ( Number_Rows > Number_Deleted_Rows )
THEN ( ( Number_Deleted_Rows/Number_Rows ) * 100.00 )
WHEN ( Number_Deleted_Rows > Number_Rows )
THEN ( ( Number_Rows/Number_Deleted_Rows ) * 100.00 )
END
, 5 , 2 )
AS Percent_Deleted
, Data_Size
, COALESCE( CHAR( DATE( Last_Used_Timestamp ) ), ' Never' ) AS
Last_Used
, COALESCE( CHAR( DATE( Last_Change_Timestamp ) ), ' Never' ) AS
Last_Chgd
, COALESCE( CHAR( DATE( Last_Save_Timestamp ) ), ' Never' ) AS
Last_Saved
, COALESCE( CHAR( DATE( Last_Restore_Timestamp ) ), ' Never' ) AS
Last_Restored
FROM qsys2.systablestat
WHERE Table_Schema = 'MYLIB'
For example, there is one table that has Number_Rows = 776 and
Number_Deleted_Rows = 694. The Algorithm_Used is returned correctly as
'4' but the SQL statement returns 0.00 for Percent_Deleted.
(I added the Algorithm_Used column because I needed to see if the tests
were activating correctly--and, they are except that I've yet to
encounter an instance of '5').
I tried CASTing Percent_Deleted as DECIMAL but that still returns zero
values.
Is it something obvious that I'm just overlooking?
This mailing list archive is Copyright 1997-2026 by midrange.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 on our policy page. If you have questions about this, please contact
[javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.