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
1,703 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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 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 142

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 142

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

808 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