Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1713
  • Last Modified:

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

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
goldstar
Asked:
goldstar
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
goldstarAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
goldstarAuthor Commented:
I want to limit the 1st select statement to a specific collection (in SMS).
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now