?
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,709 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

762 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