Regarding the original SELECT statement: I suspect there may be an empty collocation group that is messing up the results. Although Bill's is certainly the more optimal approach, I suspect that this will also work (better):
select node_name from nodes where node_name not in (select node_name from collocgroup where node_name is not null) Best regards, - Andy ____________________________________________________________________________ Andrew Raibeck | Tivoli Storage Manager Level 3 Technical Lead | [email protected] IBM Tivoli Storage Manager links: Product support: http://www.ibm.com/support/entry/portal/Overview/Software/Tivoli/Tivoli_Storage_Manager Online documentation: https://www.ibm.com/developerworks/mydeveloperworks/wikis/home/wiki/Tivoli +Documentation+Central/page/Tivoli+Storage+Manager Product Wiki: https://www.ibm.com/developerworks/mydeveloperworks/wikis/home/wiki/Tivoli +Storage+Manager/page/Home "ADSM: Dist Stor Manager" <[email protected]> wrote on 2014-09-11 09:01:03: > From: "Lee, Gary" <[email protected]> > To: [email protected] > Date: 2014-09-11 09:01 > Subject: Re: sql question > Sent by: "ADSM: Dist Stor Manager" <[email protected]> > > Thanks William. > Don't know how I overlooked collocgroup_name in the nodes table. > > Makes life easier. > > Also confirmed what I thought. I should see 122 results from my > previous query. > > > -----Original Message----- > From: ADSM: Dist Stor Manager [mailto:[email protected]] On > Behalf Of William Sefranek > Sent: Thursday, September 11, 2014 8:56 AM > To: [email protected] > Subject: Re: [ADSM-L] sql question > > Gary, > > Since the field COLLOCGROUP_NAME is in the NODES table you could use the > below queries as a sanity check to help verify the results you are > seeing from your collocation sub-query. > > select NODE_NAME, COLLOCGROUP_NAME from nodes > > select count(*) from nodes where COLLOCGROUP_NAME is NULL > > Hope this helps, > Bill > > On 9/11/2014 8:18 AM, Lee, Gary wrote: > > Nick: > > > > Just tried a cut and paste directly into the admin client. > > > > Same results, > > > > Anr2034E no results match this criteria > > > > However, a select count(node_name) from collocgroup gives 74 > > > > And select count(*) from nodes gives 196 > > > > I'm baffled > > > > > > -----Original Message----- > > From: ADSM: Dist Stor Manager [mailto:[email protected]] On > Behalf Of Nick Marouf > > Sent: Thursday, September 11, 2014 8:08 AM > > To: [email protected] > > Subject: Re: [ADSM-L] sql question > > > > Hi, > > It seems to work ok for me, I added a "-" for continuation. TSM on 6.3.0 on > > AIX 7, and TSM 6.3.4 on Redhat 5 and 6 > > > > Select a.node_name from nodes a where - > > a.node_name not in (select b.node_name from collocgroup b) > > > > > > On Thu, Sep 11, 2014 at 5:51 AM, Lee, Gary <[email protected]> wrote: > > > >> I have run myself ragged on this one. > >> I would have sworn it worked yesterday morning. > >> > >> Tsm server 6.2.5 under RHEL 6.1 > >> > >> I am trying to get a list of nodes not in a collocgroup. > >> Statement follows: > >> > >> Select a.node_name from nodes a where > >> a.node_name not in (select b.node_name from collocgroup b) > >> > >> Thanks foor any assistance. > >> Its probably simple, and I'm just not seeing it. > >> > > -- > William Sefranek > University of Buffalo > Enterprise Infrastructure Services > (716)645-5116
