Search Problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Search Problem

Author

Topic

RJ256
Starting Member

3 Posts

Posted - 2009-03-11 : 07:48:02

Hi Guys
I have been trying to create a simple query and my mind has got a bit stuck.

Basically I am searching a table which contains all the applications installed on the network joined this table to computer table
What I want to do is search for application named %Nortel% installed on all computers and from that list I want to search if those computers have Citrix% installed with a Yes/No row.
The first problem I have is that it is only returning computers with both Nortel and Citrix also need the computers with just Nortel as well. I tried a few things like creating a join from #N
The second issue is creating the Yes No if Citrix is installed with a Citrix % (don’t think it can be done with a case) have been trying to get a substring to work.
Any help would be really appreciated

select distinct
c.name
into
#N
from Inv_AeX_OS_Add_Remove_Programs a
join computer c on c.guid = a._resourceguid
where
a.name like '%Nortel%'

select distinct
c.Name
,case ISNULL(a.name,'') when 'Citrix%' then 'Yes' else 'No' end [Citrix Presentation Server Client Installed]

from Inv_AeX_OS_Add_Remove_Programs a
join computer c on c.guid = a._resourceguid
WHERE
c.name in (select * from #N)
and a.name not like 'citrix%'

order by c.name
drop table #N

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 07:58:58

See http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

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

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-11 : 08:08:32

Select name, [Citrix Presentation Server Client Installed] from (
select distinct
c.name, case ISNULL(a.name,'') when 'Citrix%' then 'Yes' else 'No' end [Citrix Presentation Server Client Installed]

from Inv_AeX_OS_Add_Remove_Programs a
join computer c on c.guid = a._resourceguid
where
a.name like '%Nortel%'
)
where [Citrix Presentation Server Client Installed] ='Yes'

RJ256
Starting Member

3 Posts

Posted - 2009-03-11 : 13:25:06

Thanks for the new code not sure it is quite right as it seems come up with a syntax error around the second 'where' will have another look at it tomorrow

Cheers
RJ

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-11 : 13:30:54

quote:
Originally posted by RJ256

Thanks for the new code not sure it is quite right as it seems come up with a syntax error around the second 'where' will have another look at it tomorrow

Cheers
RJ

its because of missing alias


Select name, [Citrix Presentation Server Client Installed] from (
select distinct
c.name, case ISNULL(a.name,'') when 'Citrix%' then 'Yes' else 'No' end AS  [Citrix Presentation Server Client Installed] 

from Inv_AeX_OS_Add_Remove_Programs a
join computer c on c.guid = a._resourceguid
where 
a.name like '%Nortel%'
)t
where [Citrix Presentation Server Client Installed] ='Yes'

RJ256
Starting Member

3 Posts

Posted - 2009-03-24 : 07:30:06

Have problem with the code in that you can't use 'Citrix%' as it does not recognise the % as a wildcard instead wants an exact match like Citrix Presentation Server but I need to include all variants of citrix.

c.name, case ISNULL(a.name,'') when 'Citrix%' then 'Yes' else 'No' end AS [Citrix Presentation Server Client Installed]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-25 : 13:03:16

make it

c.name, case when ISNULL(a.name,'') like 'Citrix%' then 'Yes' else 'No' end AS [Citrix Presentation Server Client Installed]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources