IF Statement within a Select Query ??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

IF Statement within a Select Query ???

Author

Topic

jgn1013
Starting Member

24 Posts

Posted - 2009-06-11 : 13:22:15

I'm using openxml to insert data into table

sample of xml:
declare @cmldoc varchar(8000)
declare @cmlHandle INT
Begin
set @xmlDoc =
'
<root>
<person>
<name>John Doe</name>
<address>123 Somewhere Rd</address>
<address>suite B</address>
<city>LA</city>
<state>CA</state>
<person>
<contactinfo>
<phonenumber type="home">
<areacode>475</areacode>
<telnumber>1234567</telnumber>
</phonenumber>
<phonenumber type="cell">
<areacode>475</areacode>
<telnumber>6451234</telnumber>
</phonenumber>
<contactinfo>
</root>
'

EXEC sp_xml_preparedocument @xmlHandle Output, @xmlDoc

Insert into Table1
Select * from OpenXML (@xmlHandle, '/root',2) WITH
(

name nvarchar(50) './person/name',
address nvarchar(50) './person/address',
.... etc...
**How do I or can I add an if statement here
(if './contactinfo/phonemunber/@type' = 'home'
***then add the following
homephone nvarchar(50) './contactinfo/phonenumber/areacode' + './contactinfo/phonenumber/telnumber',)
else
otherphone nvarchar(50) './contactinfo/phonenumber/areacode' + './contactinfo/phonenumber/telnumber',)

)

EXEC sp_sml_removedocument @xmlHandle
End

How or can I do an if/else statement in the middle of the "Select" statement.

TIA

yonabout
Posting Yak Master

112 Posts

Posted - 2009-06-15 : 06:48:26

I'm not sure of the exact syntax you'd need to get it going with the xml, but something like this?

select name,
address
case when type = 'home' then areacode + telnumber end as homephone,
case when type in ('cell', 'other') then areacode + telnumber end as otherphone
from [the xml stuff]

Cheers,

Yonabout

jgn1013
Starting Member

24 Posts

Posted - 2009-06-15 : 14:01:11

Thanks for the help, this is what I've gotten so far:

select name,address,
case when [type] = 'home' then [areacode] + [phone] end as [phonenumber],
case when [type] ='cell' then [areacode] + [phone] end as [otherphone]
from(
--Insert into Table1
Select * from OpenXML (@xmlHandle, '/root',2) WITH
(

name nvarchar(50) './person/name',
address nvarchar(50) './person/address',
[type] nvarchar(50) './contactinfo/phonenumber/@type',
[areacode] nvarchar(50) './contactinfo/phonenumber/areacode',
[phone] nvarchar(50) './contactinfo/phonenumber/telnumber'

)
)x

my results are:
Name......Address............phonenumber....otherphone
John Doe..123 somewhere rd...4751234567.....null

or If i do this

select name, address,
case when [type] = 'home' then [areacode] + [phone] end as [phonenumber],
case when [type] in ('cell', 'other') then [areacode] + [phone] end as [otherphone]
from
(
select * from OpenXML (@xmlHandle, '/root/contactinfo/phonenumber', 2) WITH
(
name nvarchar(50) '../../person/name',
address nvarchar(50) '../../person/address',
[type] nvarchar(50) './@type',
[areacode] nvarchar(50) './areacode',
[phone] nvarchar(50) './telnumber'

)
)x
My results are:
Name......Address............phonenumber....otherphone
John Doe..123 somewhere rd...4751234567.....null
John Doe..123 somewhere rd...null...........4756451234

how do i get this result:
Name......Address............phonenumber....otherphone
John Doe..123 somewhere rd...4751234567.....4756451234

TIA

yonabout
Posting Yak Master

112 Posts

Posted - 2009-06-16 : 05:01:50

Hi,

There's probably a better / more elegant way of doing this, but I don't know what it is.

I do an isnull on the case statement and replace the nulls with zeros, then once you've got that you can sum the numbers and group by the name and address.e.g.

create table #xmlsource 
(name varchar(50), 
address varchar(50),
type varchar(10),
areacode int,
telnumber int)

insert into #xmlsource
select 'bob', 'bobs house', 'home', 0123, 456789 union 
select 'bob', 'bobs house', 'cell', 3210, 987654 union
select 'jim', 'jims house', 'home', 0123, 987654 union
select 'tim', 'tims house', 'home', 0147, 258369 union
select 'tim', 'tims house', 'cell', 0369, 852741 union
select 'jane', 'janes house', 'cell', 0159, 326487 


select
name,
address,
sum(isnull(case when type = 'home' then areacode + telnumber end, 0)) as phonenumber,
sum(isnull(case when type = 'cell' then areacode + telnumber end, 0)) as otherphone
from #xmlsource

group by 
name, address

Cheers,

Yonabout

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 05:10:18

[code]declare @xmldoc varchar(8000)
declare @xmlHandle INT

set @xmlDoc =
'
<root>
<person>
<name>John Doe</name>
<address>123 Somewhere Rd</address>
<address>suite B</address>
<city>LA</city>
<state>CA</state>
</person>
<contactinfo>
<phonenumber type="home">
<areacode>475</areacode>
<telnumber>1234567</telnumber>
</phonenumber>
<phonenumber type="cell">
<areacode>475</areacode>
<telnumber>6451234</telnumber>
</phonenumber>
</contactinfo>
</root>
'

EXEC sp_xml_preparedocument @xmlHandle Output, @xmlDoc

SELECT name,
address,
max(phonenumber) AS phonenumber,
max(otherphone) as otherphone
From (
select name,
address,
case when [type] = 'home' then [areacode] + [phone] end as [phonenumber],
case when [type] in ('cell', 'other') then [areacode] + [phone] end as [otherphone]
from OpenXML (@xmlHandle, '/root/contactinfo/phonenumber', 2)
WITH (
name nvarchar(50) '../../person/name',
address nvarchar(50) '../../person/address',
[type] nvarchar(50) './@type',
[areacode] nvarchar(50) './areacode',
[phone] nvarchar(50) './telnumber'
) AS x
) AS w
group by name,
address
order by name,
address

EXEC sp_xml_removedocument @xmlHandle[/code]

E 12°55'05.63"
N 56°04'39.26"

yonabout
Posting Yak Master

112 Posts

Posted - 2009-06-16 : 06:57:29

Yep.

That's what I meant!

Cheers,

Yonabout

jgn1013
Starting Member

24 Posts

Posted - 2009-06-16 : 09:01:59

Peso thanks again, you always pull through when I need help. Vonabout, thank for getting me started in the right direction. I appreciate all your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources