Condition based display of select using case

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 2005 Forums

Transact-SQL (2005)

Condition based display of select using case

Author

Topic

csri
Starting Member

45 Posts

Posted - 2008-07-07 : 10:17:23

Dear All

I have a Mnemonicinfo table with columns(Mnemname,TerminalType,Routingcode,ori,ipaddress,queuename)
1000 rows are there inside the table
I have to display this select statment if Ipaddress is NULL then
Ipaddress column should not be displayed along with queuename column

select MnemName,TerminalType,RoutingCode,Ori
from mnemonicinfo

if it is not null then I have to display Ipaddress and queuename along with other columns

select MnemName,TerminalType,RoutingCode,Ori,Ipaddress,queuename

Can anyone tell me how to do this.urgent please

Thanks
sri

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-07 : 10:21:37

can't you perform this checking on your front end and decide to display or not accordingly ?

KH
[spoiler]Time is always against us[/spoiler]

csri
Starting Member

45 Posts

Posted - 2008-07-07 : 10:24:53

Hi khtan
I was asked by client to do it at back end in a stored procedure.After this from stored procedure itself I need to generate a xml document using for xml path.So need to be done in back end only.Can you please tell me how to do this.

Thanks
sri

csri
Starting Member

45 Posts

Posted - 2008-07-07 : 10:32:36

Dear All
Any Idea on how to do this so that I can try.urgent

Thanks
sri

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-07 : 10:42:30

you can't have a query to conditionally returns only 4 or 6 columns.

Will it do for you if the query also return queuename as null when Ipaddress is null ?

KH
[spoiler]Time is always against us[/spoiler]

csri
Starting Member

45 Posts

Posted - 2008-07-07 : 10:49:18

Hi khtan
When Ipaddress is null I don't want to display queuename column at all,because I have to
show in xml file the queuename column only when Ipaddress is not null.all other columns must display their data.
eg
when Ipaddress is not null then it generates a xml document like this
<Terminal>
<MnemName>BLU4</MnemName>
<RoutingCode>AX</RoutingCode>
<Ori>hh0310403</Ori>
<QueueName>.\BLU4</QueueName>
<IpAddress>160.168.122.13</IpAddress>
</Terminal>

when Ipaddress is null the xml file must look like this.all other columns and their data must be displayed accordingly.
<Terminal>
<MnemName>BLU4</MnemName>
<RoutingCode>AX</RoutingCode>
<Ori>OH0310403</Ori>
</Terminal>

Thanks
sri

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-07 : 10:55:09

How do you convert the result into XML ?

KH
[spoiler]Time is always against us[/spoiler]

csri
Starting Member

45 Posts

Posted - 2008-07-07 : 10:59:22

Hi khtan
Using the for xml path.Path in sql server 2005 which outputs xml file.My question is this one only.

I have a Mnemonicinfo table with columns(Mnemname,TerminalType,Routingcode,ori,ipaddress,queuename)
1000 rows are there inside the table
I have to display this select statment if Ipaddress is NULL then
Ipaddress column should not be displayed along with queuename column

select MnemName,TerminalType,RoutingCode,Ori
from mnemonicinfo

if it is not null then I have to display Ipaddress and queuename along with other columns

select MnemName,TerminalType,RoutingCode,Ori,Ipaddress,queuename
Any help is appreciated.

Thanks
sri

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-07 : 11:48:24

[code]
select MnemName,TerminalType,RoutingCode,Ori, Ipaddress, case WHEN Ipaddress is not null then queuename else NULL end as queuename
from mnemonicinfo
[/code]

KH
[spoiler]Time is always against us[/spoiler]

csri
Starting Member

45 Posts

Posted - 2008-07-07 : 12:11:20

Hi Khtan
Many Many Thanks for your answer.I think when keyword is missing in your statement.Thank you very much.Really appreciate your answer.

select MnemName,TerminalType,RoutingCode,Ori, Ipaddress, case WHEN Ipaddress IS NOT NULL then queuename else NULL end as queuename
from mnemonicinfo

Thanks
sri

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-07 : 12:26:26

yes. I missed the WHEN keyword

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources