Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

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,710 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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.
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

618 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