Case in select statement not returning calculated value

Posted on 2006-04-11
Last Modified: 2008-01-09
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:

   '[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?
Question by:bjones8888
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    please try this:

       '[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
    LVL 50

    Accepted Solution

    the calulation as you state it


    is this what you mean by "null"

    can you explain what the calculation is trying to do?


    Author Comment

    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.

    Author Comment

    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.
    LVL 28

    Expert Comment

    --Try this one
       '[NetMeters]' =

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

    (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

    Author Comment

    Thanks.  I'll have to wait till the morning to give this a try.  (no longer on client site).  I'll let you know.
    LVL 50

    Expert Comment

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


    Author Comment

    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

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now