Re: Need assistance with SQL statement over QSYS2.SYSTABLESTAT

[Robert Clay <zreclay@xxxxxxxxx>]

Birgitta,

Thanks for the input.  That's less wordy, to be sure.

/BTW, you may want to consider changing the first few letters in the 
Subject field on your replies from "AW:" to "Re:" because all of your 
replies appear in a different place from the original message and 
replies (at least, in Thunderbird, they do).  I almost missed this./

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:07 PM, Birgitta Hauser wrote:
> Hi,
>
> Number_Rows and Number_Deleted_Rows are both defined as Integer (Big
> Integer).
> When dividing 2 integers, the result will be again an integer value without
> rounding, i.e. the result is truncated.
>
> Just rewrite the THEN clauses as follows and it will work correctly (since
> 100.00 is a decimal value the result will be a float/decimal value either):
> ...
> THEN (100.00 *  Number_Deleted_Rows / Number_Rows )
>                WHEN ( Number_Deleted_Rows > Number_Rows )
> THEN ( 100.00 * Number_Rows / Number_Deleted_Rows )
>
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
>
> -----Ursprüngliche Nachricht-----
> Von: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Robert Clay
> Gesendet: Monday, 16.9 2013 18:20
> An: midrange-l@xxxxxxxxxxxx
> Betreff: Need assistance with SQL statement over QSYS2.SYSTABLESTAT
>
> [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
>
>
>
>
> --
> "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
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
> or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
> moment to review the archives at http://archive.midrange.com/midrange-l.