I don't think the third when has anything to do with divide by zero. Looks like it is an optimization to shortcut the calculation in the else given NUMBER_ROWS = NUMBER_DELETED_ROWS. Though you might want to do some testing to determine if it is a premature optimization or not.
STAR BASE Consulting, Inc.
-----Scott Mildenberger <SMildenberger@xxxxxxxxxxxxxxxxxx> wrote: -----
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
From: Scott Mildenberger <SMildenberger@xxxxxxxxxxxxxxxxxx>
Date: 09/16/2013 04:51PM
Subject: RE: Need assistance with SQL statement over QSYS2.SYSTABLESTAT
Looks good and I think you are right about not needed the third WHEN because the first one will stop the divide by zero case.
Davis Transport Inc.
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Robert Clay
Sent: Monday, September 16, 2013 1:45 PM
To: midrange-l@xxxxxxxxxxxx; midrange-l@xxxxxxxxxxxx
Subject: Re: Need assistance with SQL statement over QSYS2.SYSTABLESTAT
Combined with the other suggestions, how about this?
WHEN ( Number_Deleted_Rows = 0 ) THEN 0
WHEN ( Number_Rows = 0 ) AND ( Number_Deleted_Rows > 0 ) THEN
WHEN Number_Rows = Number_Deleted_Rows THEN
ELSE (100 * Number_Deleted_Rows / ( Number_Rows + Number_Deleted_Rows ) )
AS DECIMAL( 5 , 2 )
) AS Percent_Deleted
Might not even need the third WHEN.
"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 2:40 PM, Scott Mildenberger wrote:
Your first two whens can be combined into one
WHEN Number_Deleted_Rows = 0 THEN 0
Also, your very last when doesn't return 'Percent deleted', consider 1 Row and 5 deleted. Returns 20%. The more deleted the smaller the percent, is that what you really want? You may be able to use the when before that as an ELSE catchall.
Davis Transport Inc.
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,
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l