Solved

sql function case statement

Posted on 2013-01-23
15
426 Views
Last Modified: 2013-01-25
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)
0
Comment
Question by:dwiseman3
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 167 total points
ID: 38812473
>> Is there a problem using a case statement [this way] in a user defined function? <<

No; CASE statements are fully supported in functions.

You must have some other issue.
0
 
LVL 12

Expert Comment

by:duttcom
ID: 38812505
I think the problem might be in your use of ISNULL.

ISNULL is a binary value so isnull(leveln,0) = 0 means isnull(leveln,0) = false (leveln is NOT null)

Try-

isnull(leveln,0) = 1
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38812534
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".
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 12

Assisted Solution

by:duttcom
duttcom earned 166 total points
ID: 38812556
Yes, it does mean false when it is used like that.

As you say, ISNULL(leveln, 0) means that is leveln is NULL when leveln = 0, however by using ISNULL(field,x) = 1 or 0 you are stating whether or not the the field's ISNULL 'property' is either true or false. ISNULL(field, 0) cannot = 3 as in a value comparison.

You could also just use-

isnull(leveln,0) then lvl0 else lvl1

and not use =0 or =1 at all
0
 
LVL 39

Assisted Solution

by:appari
appari earned 167 total points
ID: 38812841
it helps if you can post sample data and the result you want.
duttcom: are you sure what you are writing about isnull fuction in sqlserver?
0
 
LVL 12

Expert Comment

by:duttcom
ID: 38812954
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
0
 
LVL 39

Expert Comment

by:appari
ID: 38812963
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.
0
 
LVL 12

Expert Comment

by:duttcom
ID: 38812998
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
0
 
LVL 39

Expert Comment

by:appari
ID: 38813008
the one you were talking is a property in property class in SMO namespace and it is different from transact sql isnull function.
0
 
LVL 12

Expert Comment

by:duttcom
ID: 38813043
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.
0
 
LVL 39

Expert Comment

by:appari
ID: 38813059
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.
0
 
LVL 12

Expert Comment

by:duttcom
ID: 38813065
"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
0
 
LVL 39

Expert Comment

by:appari
ID: 38813084
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)
0
 
LVL 12

Expert Comment

by:duttcom
ID: 38813131
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.
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 38813774
You can simply solve it by testing for null or 0 which will be much clearer but not much different

select @G = round(sum(cratt * 
				case 
					when (leveln IS null) OR (leveln = 0) then 
						lvl0 
					else 
						lvl1 
				end)/sum(cratt),4)

Open in new window


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)

Open in new window

0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql help 5 53
2 comma seperated list - SQL Server 12 41
SQL Query with Sum and Detail rows 2 50
SQL query with cast 38 43
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

810 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