How to Use Round And nullif together?

Question

I have a column that calculates Unit Weight (g) by doing the following calculations

SELECT ROUND (Weight,0) / nullif ([Units per Carton],0) * 454 AS [Unit Weight (g)]
FROM [Item Master]

I used the nullif to eliminate the divide by zero. but i also want to use the Round() on the divisor

i tried this:

SELECT  ROUND (Weight,0) / nullif(Round([Units per Carton],0) * 454) AS [Unit Weight (g)]
FROM [Item Master]

i tried this:

  SELECT  nullif (ROUND (Weight,0) / (Round([Units per Carton],0) * 454) AS [Unit Weight (g)]
    FROM [Item Master]

error: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'AS'.

**So how do i combine a round function with nullif on the divisor?? **

Newly Edited Code

SELECT ROUND (Weight,0) / nullif([Units per Carton],0) * 454 AS [Unit Weight (g)]
FROM [Item Master] 

SELECT ROUND (Weight / NULLIF([Units per Carton],0),0) * 454 AS [Unit Weight (g)]
FROM [Item Master]

I tried the above and the result i got is in this -> picture

The results are different i wanted the result to be 151, 227 and 227 but those results are getting replaced by 0's.

@Lamak i want the whole thing to be Rounded but only do a nullif check on the divisor so i am not dividing by zero, so i want the Unit Per Carton to be rounded and nuliif on that..... — HackGod555, Nov 18 '14 at 20:35
Why would you use / nullif ([Units per Carton],0) if want to avoid a divide by zero? — paparazzo, Nov 18 '14 at 21:39
@Blam because one can't divide a number by zero and i have units per carton as a zero value, by using nullif those values are changed to null and now i know null means those were zero values and divide by zero was not possibel, but the other columns with number like 153.4456 i want those to round up to 153 in order to do this i need to use the Round Function on the nullif, did you get that???? — HackGod555, Nov 18 '14 at 22:44

sqldoug · Answer 1 · 2015-02-04T23:15:32.800

0

Null should never be the dividend. You will not get logically consistent results dividing by nulls. Remember that a null is not a value at all, it is merely a marker for 'unknown'. (And null is not the same as 'null').

You might try a branch condition instead.

SELECT 454 * ROUND( (Weight * 1.0) / CASE WHEN [Units per Carton] = 0 THEN -1  
ELSE [Units per Carton] END , 0) AS [Unit Weight (g)]

Any resulting weights that are negative are then substitutes for 0.

Or, in a nutshell:

SELECT  @Quotient = CASE WHEN @divisor = 0 THEN NULL
                 ELSE @dividend / @divisor END

edited Feb 04 '15 at 23:15

answered Nov 19 '14 at 04:16

sqldoug

429
1
3
10

i tried your query but i am still having differences in the result which can be found in this -> [picture](http://imgur.com/jbJ0Han) The above is the expected result from access and below is the result from your query they are not the same. – HackGod555 Nov 20 '14 at 22:17
Ah, MS Access? I'm not sure if it's query capabilities are the same as a SQL Server database. – sqldoug Dec 13 '14 at 18:07

How to Use Round And nullif together?

1 Answers1