February 13, 2008 at 4:56 pm
I have a table...
FullName,AddressHome,AddressPO,CustPhone
Here's the scenario...
You can see there are two addresses. In most cases there is data in [AddressHome]. In some cases there is data in [AddressPO] and in some small cases there's data in both [AddressHome] and [AddressPO].
I need a Select query that returns my data making the following decision..
If there is data in [AddressHome] and no data in [AddressPO] I want the [AddressHome]
If there is no data in [AddressHome] and data in [AddressPO] I want the [AddressPO]
If there is data in both, I want to default to return only [AddressHome]
Is this possible without having to break the table out to seperate the criterias?
Thanks
February 13, 2008 at 5:24 pm
create table #test
(
FullName varchar(5), AddressHome varchar(10), AddressPO varchar(10), CustPhone char(10)
)
insert into #test
select 'allen', '1', null, 'any'
union
select 'betty', null, '22', 'any'
union
select 'chuck', '333', '4444', 'any'
union
select 'david', null, null, 'any'
select FullName, coalesce(AddressHome, AddressPO, '') Address, CustPhone
from #test
-- if you have empty strings instead of nulls
truncate table #test
insert into #test
select 'allen', '1', '', 'any'
union
select 'betty', '', '22', 'any'
union
select 'chuck', '333', '4444', 'any'
union
select 'david', '', '', 'any'
select FullName,
case
when AddressHome = '' and AddressPO <> '' then AddressPO
else AddressHome
end Address
, CustPhone
from #test
February 13, 2008 at 6:32 pm
This a perfect scenario for a case statement.
Select case when AddressHome = "" then AddressPO
else AddressHome end as Address
from table
This assumes the 'no data' case is signified by a blank string, if you use nulls instead, change 'AddressHome = ""' to 'AddressHome is null' in the query. Optionally, if you use nulls for the no data case, you can use the isnull function instead, like the query:
Select isnull(AddressHome,AddressPO) as Address
from table
--Ed
February 19, 2008 at 10:15 pm
Hi,
This is really a perfect example for "Case" statement.
You can try this query....
**************
create table testfeb
(
FullName varchar(5), AddressHome varchar(10), AddressPO varchar(10), CustPhone char(10)
)
Insert into testfeb(FullName,
AddressHome,
AddressPO,
CustPhone)Values('Abc','','Address1',2323)
Insert into testfeb(FullName,
AddressHome,
AddressPO,
CustPhone)Values('def','Address2',null,2323)
Insert into testfeb(FullName,
AddressHome,
AddressPO,
CustPhone)Values('jkl','Addres','Address6',2324)
Select * from testfeb
Select Case
When ((AddressHome is not null) and (AddressPO is null or AddressPO='')) then AddressHome
When ((AddressPO is not null) and (AddressHome is null or AddressHome='')) then AddressPO
When (AddressHome is not null and AddressPO is not null) then AddressHome
End as 'Address'
From testfeb
*************
Please use this select query for your scenarios.......
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply