× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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

This mailing list archive is Copyright 1997-2024 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.