Case in select statement not returning calculated value

I'm trying to execute a select statement but I'm getting null values where I expect numbers.  (This is in an Access ADP.)

Here's the (simplified) statement:

SELECT TOP 100 PERCENT
   '[NetMeters]' = CASE WHEN JumboWidthM * Length * RollsPerCase * [Case Count] = 0 THEN 0
                   ELSE (Width / (1000 * Length * RollsPerCase * [Case Count])) / JumboWidthM END
FROM tblConvertingMetric

The values in the fields for one particular record are:  
Width = 24
Length = 55
RollsPerCase = 36
Case Count = 128
JumboWidthM = 1.537

This is being executed on a table with 47k records.  In all situations it returns a blank for NetMeters.  Admittedly, there are lots of records with zeros for some of these values, so I expected a 0 for most records, but I'm not getting that.

I don't mind getting junk where there's junk in any of the components of the formula, but I expect real values where there a valid numbers.  Any ideas why I'm getting blank or null for records that have valid numbers for all the input fields?
bjones8888PresidentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:

SELECT TOP 100 PERCENT
   '[NetMeters]' = CASE WHEN JumboWidthM * Length * RollsPerCase * [Case Count] = 0 THEN 0
                   ELSE (cast( Width as numeric(15,5)) /  (1000 * Length * RollsPerCase * [Case Count])) / JumboWidthM END
FROM tblConvertingMetric
LowfatspreadCommented:
the calulation as you state it
gives

0.000000

is this what you mean by "null"

can you explain what the calculation is trying to do?

 

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bjones8888PresidentAuthor Commented:
Interesting. Although it produced the same results (blank for all rows), this indicates to me that SQL Server is attempting to make a judgment on the fly as to what data type to assign to the calculated field.  Is that a correct assumption?

I even tried casting all the fields - but no better results.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

bjones8888PresidentAuthor Commented:
It's trying to calculate the square meters of tape from a giant roll being cut into the size you buy in the stores.  We have a jumbo roll that is 1.537 meters wide.  Width is the width (in millimeters) of the individual end product roll.  Length is the length of the roll, etc.  We're trying to calculate total output from the jumbo roll.

Some records in the table contain junk, as far as this formula is concerned.  Since there is a division as part of the formula, I have to deal with divide by zero exceptions.

The results as viewed in Access are blank.  Not 0.0000.  

Since I don't know which of the input fields could be zero, I'm multiplying them all together in my case statement.  If any of them are zero, the result is zero, and then I want to have a zero result for that record, otherwise, I want the calculated value.
imran_fastCommented:
--Try this one
--==============
SELECT TOP 100 PERCENT
   '[NetMeters]' =

CASE WHEN
   (isnull(JumboWidthM,0) * isnull(Length,0) * isnull(RollsPerCase,0) * isnull([Case Count],0))
       = 0 THEN 0
 
ELSE

(convert(numeric(9,2),isnull(Width,0)) / convert(numeric(9,2),(1000 * isnull(Length,0) * isnull(RollsPerCase,0) * isnull([Case Count],0)))) / isnull(JumboWidthM,0) END
FROM tblConvertingMetric
bjones8888PresidentAuthor Commented:
Thanks.  I'll have to wait till the morning to give this a try.  (no longer on client site).  I'll let you know.
LowfatspreadCommented:
yes sql server looks at the data types of the columns and uses what it considers to be the best fit for the result...
you will often have to "Cast" the column/static values to produce the result to the desired precision...

e.g. integer divided by integer will result in an integer so you'll lose decimal place information ie. 5/2 = 2 not 2.5


sorry can you explain more about the dimensions i'm trying to visualise it on the roller/cutter

e.g. is length the length of the eventual product or the length of the jumbo roll and what metrics...

 
 
bjones8888PresidentAuthor Commented:
Thank you to angelIII and Lowfatspread.  I actually had an error in the calculation (indicated by Lowfatspread) but it was the cast( ) function that helped me see it.

The wrong formula:
 Width /  (1000 * Length * RollsPerCase * [Case Count]) / JumboWidthM

The right one:
 Width /  1000 * Length * RollsPerCase * [Case Count] / JumboWidthM

Cast( ) helped me see that I began to get numbers, but they were miniscule.  Thank you for your help and I'm red-faced for the simple arithmetic error.  :-0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.