How to get this in decimal value instead of null

问题:

i am trying to get value in Decimal in this query but unable to get i am getting NULL Value

SELECT CAST(CAST(CAST(SUM(CAST(0 AS INT)) AS DECIMAL(10, 2)) * 100 
/ CAST(NULLIF(SUM(CAST(0 AS INT)) 
+ SUM(CAST(0 AS INT)) + 
SUM(CAST(0 AS INT)), 0)
 AS DECIMAL(10, 2)) AS DECIMAL(10, 2)) AS DECIMAL)

how to get output

0.00

instead of null in this query

回答1:

Use ISNULL built in function:

ISNULL(CAST(CAST(CAST(SUM(CAST(0 AS INT)) AS DECIMAL(10, 2)) * 100 / CAST(NULLIF(SUM(CAST(0 AS INT)) + SUM(CAST(0 AS INT)) + SUM(CAST(0 AS INT)), 0) AS DECIMAL(10, 2)) AS DECIMAL(10, 2)) AS DECIMAL), 0) 

http://technet.microsoft.com/es-es/library/ms184325.aspx

回答2:

I don't even start to understand all the conversions that you are doing, but you can just simply use ISNULL on that expression:

SELECT ISNULL(<all your calculations here>,0)

回答3:

The entire expression is evaluating to NULL because you are using the following code.

NULLIF  (
    SUM (   
        CAST(0 AS INT)
    ) + 
    SUM(    
        CAST(0 AS INT)
    ) + 
    SUM(    CAST(0 AS INT)
    ), 
    0
) 

I'm going to break this down a little to make it easier to understand.

CAST(0 AS INT) evaluates to 0

SUM( CAST( 0 AS INT ) ) evaluates to 0

SUM( CAST(0 AS INT) ) + 
SUM( CAST(0 AS INT) ) + 
SUM( CAST(0 AS INT) ) also evaluates to 0

So wrapping that whole expression in a NULLIF (expression, 0) means it comes out as NULL.

That null then propagates through the rest of the calculation and causes the whole expression to evaluate to NULL.

If you want it to be 0 you should wrap the whole expression in a ISNULL or a COALESCE statement or you could remove the NULLIF check although it's a little unclear what you are actually trying to achieve.

回答4:

Way too many CASTs in that SQL

Use a CASE if you don't want to use ISNULL, which is needed because you have NULLIF

SELECT 
  CAST(
    CASE
      WHEN SUM(CAST(0 AS INT)) + SUM(CAST(0 AS INT)) + SUM(CAST(0 AS INT)) = 0 THEN 0
      ELSE SUM(CAST(0 AS INT)) * 100.00 / (SUM(CAST(0 AS INT)) + SUM(CAST(0 AS INT)) + SUM(CAST(0 AS INT)))
  END
  AS DECIMAL(10, 2))

Howver, anything divided by zero is undefined or infinity: not zero.