case statements

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

LVL 3
lisa_mcAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
rajvjaConnect With a Mentor Commented:
(case when i.add_3  is null then i.add_2 end as town)
 
0
 
wdosanjosConnect With a Mentor Commented:
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
 
lisa_mcAuthor Commented:
hi rajvja

how do i fit this into the code shown above as I need the other conditions in the case statement
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
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
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
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
 
lisa_mcAuthor Commented:
Ive split the points thanks guys
0
All Courses

From novice to tech pro — start learning today.