Re: Need assistance with SQL statement over QSYS2.SYSTABLESTAT

[Robert Clay <zreclay@xxxxxxxxx>]

Update:

Perhaps that fact that the columns in QSYS2.SYSTABLESTAT are defined as 
BIGINT has something to do with this?


"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 12:19 PM, Robert Clay wrote:
> [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