Solved

case statements

Posted on 2011-02-22
6
219 Views
Last Modified: 2012-05-11
Hi experts

I have a problem and I hope someone can help

I have attached a case statement which I am working on and im having a problem with the null values

What I would like to do is when i.add_3 is null then I want the value of i.add_2 to be placed in the new column 'town'

Can anyone see where I am going wrong

Thanks
(case i.add_3 

	when 'Co Londonderry' then i.add_2
	when 'Co Antrim' then i.add_2
	when 'County Londonderry' then i.add_2
	when 'Co Tyrone' then i.add_2
	when 'Co Derry' then i.add_2
--	when isnull(i.add_3,i.add_2) -- this line is incorrect
	else i.add_3 end) as 'town',

Open in new window

0
Comment
Question by:lisa_mc
6 Comments
 
LVL 11

Accepted Solution

by:
rajvja earned 165 total points
ID: 34952727
(case when i.add_3  is null then i.add_2 end as town)
 
0
 
LVL 23

Assisted Solution

by:wdosanjos
wdosanjos earned 165 total points
ID: 34952800
Try:

(case i.add_3 

	when 'Co Londonderry' then i.add_2
	when 'Co Antrim' then i.add_2
	when 'County Londonderry' then i.add_2
	when 'Co Tyrone' then i.add_2
	when 'Co Derry' then i.add_2
	else isnull(i.add_3,i.add_2)  
        end) as 'town',

Open in new window

0
 
LVL 3

Author Comment

by:lisa_mc
ID: 34952805
hi rajvja

how do i fit this into the code shown above as I need the other conditions in the case statement
0
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.

 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 170 total points
ID: 34952827
try:

(case  
      when i.add_3='Co Londonderry' then i.add_2
      when i.add_3='Co Antrim' then i.add_2
      when i.add_3='County Londonderry' then i.add_2
      when i.add_3='Co Tyrone' then i.add_2
      when i.add_3='Co Derry' then i.add_2
      when i.add_3 is null then i.add_2 -- this line is incorrect
      else i.add_3 end) as 'town',
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 170 total points
ID: 34952848
is this doing the same thiong?

(
case  
when  i.add_3 is null or i.add_3 in ('Co Londonderry','Co Antrim','County Londonderry','Co Tyrone','Co Derry') then i.add_2
else i.add_3 end
) as 'town',
0
 
LVL 3

Author Closing Comment

by:lisa_mc
ID: 34952937
Ive split the points thanks guys
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

776 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