×

Good News Everybody!

The new search engine is LIVE!

Please report any problems to david (at) midrange.com.




[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?

Thanks in advance,
Robert





This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.