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.
Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx
-----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
Robert,
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.
Scott Mildenberger
Programmer/Analyst
Davis Transport Inc.
Missoula, MT
406-728-5510 x128
-----Original Message-----
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
Scott,
You're right.
Combined with the other suggestions, how about this?
CAST(CASE
WHEN ( Number_Deleted_Rows = 0 ) THEN 0
WHEN ( Number_Rows = 0 ) AND ( Number_Deleted_Rows > 0 ) THEN
100.00
WHEN Number_Rows = Number_Deleted_Rows THEN
50.00
ELSE (100 * Number_Deleted_Rows / ( Number_Rows + Number_Deleted_Rows ) )
END
AS DECIMAL( 5 , 2 )
) AS Percent_Deleted
Might not even need the third WHEN.
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 2:40 PM, Scott Mildenberger wrote:
Robert,
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.
Scott Mildenberger
Programmer/Analyst
Davis Transport Inc.
Missoula, MT
406-728-5510 x128
--
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.
midrange.com
