dwiseman3
asked on
sql function case statement
This formula is only using the lvl1 value. It is not using lvl0 when leveln = 0
Is there a problem using a case statement this way in a user defined function?
select @G = round(sum(cratt * case when isnull(leveln,0) = 0 then lvl0 else lvl1 end)/sum(cratt),4)
Is there a problem using a case statement this way in a user defined function?
select @G = round(sum(cratt * case when isnull(leveln,0) = 0 then lvl0 else lvl1 end)/sum(cratt),4)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No; the "= 0" doesn't mean "false". ISNULL(leveln, 0) means that is leveln is NULL, substitute the value 0 in its place. The "= 0" is a value comparison, just like any other value comparison in SQL, such as "CASE WHEN A = 3".
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ISNULL is a boolean property being tested. Either the value is null or not, therefore ISNULL can only ever resolve to 0 or 1, true or false. ISNULL is a function of a field's property and can therefore be used as either (and both in this case).
The OP was using ISNULL as both a function (isnull(leveln,0)) AND then testing that property (by using the = 0), which was then negating the value of the function.
The way the original query is written, if the value of leveln = 0, then the following occurs -
isnull(leveln,0) by itself evaluates to 1 or true, so the value of the field IS null
But then by adding =0, the ISNULL value of the field is being tested again. The test is then being tested.
isnull(leveln,0) = 0 evaluates to 1 = 0 and the end result is 0
The OP was using ISNULL as both a function (isnull(leveln,0)) AND then testing that property (by using the = 0), which was then negating the value of the function.
The way the original query is written, if the value of leveln = 0, then the following occurs -
isnull(leveln,0) by itself evaluates to 1 or true, so the value of the field IS null
But then by adding =0, the ISNULL value of the field is being tested again. The test is then being tested.
isnull(leveln,0) = 0 evaluates to 1 = 0 and the end result is 0
duttcom: I suppose you are getting confused with isnull function in other languages. see BOL topic on isnull function, http://msdn.microsoft.com/en-us/library/ms184325(v=sql.90).aspx
there is no problem with the usage of isnull function as posted by OP.
there is no problem with the usage of isnull function as posted by OP.
I use the ISNULL function that way all the time in SQL for different purposes. The OP is using the function (correctly, yes) but is then ALSO testing the VALUE of the function by adding =0.
When ISNULL(anything) = 1 or true, the field IS NULL
When ISNULL(anything) = 0 or false, the field is NOT NULL and contains data
Therefore the OP's query results in 1=0 and the query then thinks the field has data.
This article explains the boolean nature of the ISNULL property.
http://msdn.microsoft.com/en-au/library/microsoft.sqlserver.management.smo.property.isnull(v=sql.90).aspx
When ISNULL(anything) = 1 or true, the field IS NULL
When ISNULL(anything) = 0 or false, the field is NOT NULL and contains data
Therefore the OP's query results in 1=0 and the query then thinks the field has data.
This article explains the boolean nature of the ISNULL property.
http://msdn.microsoft.com/en-au/library/microsoft.sqlserver.management.smo.property.isnull(v=sql.90).aspx
the one you were talking is a property in property class in SMO namespace and it is different from transact sql isnull function.
It works the same way regardless. ISNULL as it is being used here returns a boolean value and the OP needs to change the query to test if the ISNULL value of the field evaluates to 1 (when the field is null) and not 0 (when the field has a value).
when isnull(leveln,0) = 0
Effectively means - when the ISNULL property of leveln is false (has a value other than 0)
when isnull(leveln,0) = 1
Effectively means - when the ISNULL property of leveln is true (has a value of 0)
Therefore the test needs to be when isnull(leveln,0) = 1 to test for when the ISNULL-ness of the field is TRUE, not false the way it currently is.
when isnull(leveln,0) = 0
Effectively means - when the ISNULL property of leveln is false (has a value other than 0)
when isnull(leveln,0) = 1
Effectively means - when the ISNULL property of leveln is true (has a value of 0)
Therefore the test needs to be when isnull(leveln,0) = 1 to test for when the ISNULL-ness of the field is TRUE, not false the way it currently is.
Don't know how to convince you.
they are two different things. the one used by OP in his question is the transact sql function and the usage syntax is correct.
ScottPletcher: request you to post your comments.
they are two different things. the one used by OP in his question is the transact sql function and the usage syntax is correct.
ScottPletcher: request you to post your comments.
"It is not using lvl0 when leveln = 0 ...
select @G = round(sum(cratt * case when isnull(leveln,0) = 0 then lvl0"
Okay, so when leveln = 0, step me through how "when isnull(leveln,0) = 0" is evaluated and should therefore use lvl0
select @G = round(sum(cratt * case when isnull(leveln,0) = 0 then lvl0"
Okay, so when leveln = 0, step me through how "when isnull(leveln,0) = 0" is evaluated and should therefore use lvl0
duttcom:
>>case when isnull(leveln,0) = 0 then lvl0 else lvl1 end
1. isnull(leveln,0) evaluates to 0 when leveln value is null and all other cases to leveln value.
2. value from step 1 is compared with 0. if the value in step 1 is 0 then returns lvl0 otherwise returns lvl1 value.
dwiseman3: may be null values in lvl0 and lvl1 is causing the problem,
try like this
select @G = round(sum(cratt * case when isnull(leveln,0) = 0 then isnull(lvl0,0) else isnull(lvl1, 0) end)/sum(cratt),4)
>>case when isnull(leveln,0) = 0 then lvl0 else lvl1 end
1. isnull(leveln,0) evaluates to 0 when leveln value is null and all other cases to leveln value.
2. value from step 1 is compared with 0. if the value in step 1 is 0 then returns lvl0 otherwise returns lvl1 value.
dwiseman3: may be null values in lvl0 and lvl1 is causing the problem,
try like this
select @G = round(sum(cratt * case when isnull(leveln,0) = 0 then isnull(lvl0,0) else isnull(lvl1, 0) end)/sum(cratt),4)
I agree it would return 0 if it were in the select clause itself e.g.
select isnull(leveln,0)
from titles
However, I believe that since it is part of 'case when' clause it will return a 1 when the value is null because the ISNULL property is being tested, not the specified null value.
I look forward to seeing how this is gets solved. Time to go home.
select isnull(leveln,0)
from titles
However, I believe that since it is part of 'case when' clause it will return a 1 when the value is null because the ISNULL property is being tested, not the specified null value.
I look forward to seeing how this is gets solved. Time to go home.
You can simply solve it by testing for null or 0 which will be much clearer but not much different
Now, anything multiplied by null is null, if you dont want you result set to contain null, you can add ISNULL to lvl1 and lvl0
select @G = round(sum(cratt *
case
when (leveln IS null) OR (leveln = 0) then
lvl0
else
lvl1
end)/sum(cratt),4)
Now, anything multiplied by null is null, if you dont want you result set to contain null, you can add ISNULL to lvl1 and lvl0
select @G = round(sum(cratt *
case
when (leveln IS null) OR (leveln = 0) then
ISNULL(lvl0, 0)
else
ISNULL(lvl1 , 0)
end)/sum(cratt),4)
ISNULL is a binary value so isnull(leveln,0) = 0 means isnull(leveln,0) = false (leveln is NOT null)
Try-
isnull(leveln,0) = 1