• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

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?
0
bjones8888
Asked:
bjones8888
2 Solutions
 
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
0
 
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?

 
0
 
bjones8888Author 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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
bjones8888Author 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.
0
 
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
0
 
bjones8888Author 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.
0
 
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...

 
 
0
 
bjones8888Author 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
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now