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 |
storman AT us.ibm DOT com

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" <ADSM-L AT VM.MARIST DOT EDU> wrote on 2014-09-11
09:01:03:

> From: "Lee, Gary" <GLEE AT BSU DOT EDU>
> To: ADSM-L AT VM.MARIST DOT EDU
> Date: 2014-09-11 09:01
> Subject: Re: sql question
> Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
>
> 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:ADSM-L AT VM.MARIST DOT EDU] On
> Behalf Of William Sefranek
> Sent: Thursday, September 11, 2014 8:56 AM
> To: ADSM-L AT VM.MARIST DOT EDU
> 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:ADSM-L AT VM.MARIST DOT EDU] On
> Behalf Of Nick Marouf
> > Sent: Thursday, September 11, 2014 8:08 AM
> > To: ADSM-L AT VM.MARIST DOT EDU
> > 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 <GLEE AT bsu DOT edu> 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