Case in select statement not returning calculated value
Posted on 2006-04-11
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
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?