[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Posted on 2008-06-10
5
Medium Priority
?
1,716 Views
Last Modified: 2013-11-21
I get the error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS" when I execute this code in SMS:
select v_R_System.Name0 from v_R_System where v_R_System.Name0 not in (select v_R_System.Name0, v_R_System.User_Name0, v_gs_environment.VariableValue0 from v_gs_environment JOIN  v_R_System ON v_GS_environment.ResourceID = v_R_System.ResourceID where v_gs_environment.Name0 LIKE 'War%')

the subquery works on it's own.
I'm trying to determine which computers do *not* have a particular environment variable on it.
0
Comment
Question by:goldstar
  • 3
  • 2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 21754778
here we go:
select s.Name0 
from v_R_System s
where not exists (select null 
        from v_gs_environment e
       where e.ResourceID = s.ResourceID 
        and e.Name0 LIKE 'War%'
   )

Open in new window

0
 

Author Comment

by:goldstar
ID: 21755001
Perfect & quick!  Great!
As a follow up, can you tell me how to modify this code to a particular collection in SMS (not the subquery but the select s.Name0)?  I want to look at only one collection.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21755028
you mean to add more conditions?
select s.Name0 
from v_R_System s
where not exists (select null 
        from v_gs_environment e
       where e.ResourceID = s.ResourceID 
        and e.Name0 LIKE 'War%'
   )
  and s.Name like 'Test%' 

Open in new window

0
 

Author Comment

by:goldstar
ID: 21755155
I want to limit the 1st select statement to a specific collection (in SMS).
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21756710
sorry, I don't know the schema of SMS, so I don't know what " a specific collection " means in terms of tables/columns ...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question