Solved

case statements

Posted on 2011-02-22
6
238 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 56

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 56

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

617 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