Re: SQL query problem on collocgroup table

Well, well, well, this looks like a bona fide bug to me. Is AIX_0_6 the
first collocation group name in the output from:

   select collocgroup_name from collocgroup

If so, this would be consistent with my findings (though I cannot say I
have looked at this exhaustively): your script works only when the
collocation group name is the first one in the list.

As a test, try running this:

   select node_name, collocgroup_name from collocgroup where
collocgroup_name in ('AIX_INACTIVE')

that should work, though I realize it isn't what you really want.

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: storman AT us.ibm DOT com

The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.

"ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU> wrote on 2005-08-26
02:12:31:

> Hi all,
>
> I'm facing some strange SQL problem while querying COLLOCGROUP table,
> and can't find out what is happening here ...
> I've built a script (q_collocgroup) which looks like :
>
> select collocgroup_name from collocgroup where node_name=upper('$1')
> select node_name, collocgroup_name from collocgroup where
> collocgroup_name in (select collocgroup_name from collocgroup where
> node_name=upper('$1'))
>
> For some nodes, the scripts works perfectly :
>
> run q_collocgroup asbrs000
>
> COLLOCGROUP_NAME
> ------------------
> AIX_0_6
>
> NODE_NAME              COLLOCGROUP_NAME
> ------------------     ------------------
> ASBRS000               AIX_0_6
> PACRS070               AIX_0_6
> PACSP030               AIX_0_6
>
>
> For other ones, the second query fails :
>
> run q_collocgroup old_pacrs111
>
> COLLOCGROUP_NAME
> ------------------
> AIX_INACTIVE
> ANR2034E SELECT: No match found using this criteria.
> ANR1462I RUN: Command script Q_COLLOCGROUP completed successfully.
>
> I tried an equivalent query :
> (SELECT COLLOCGROUP.NODE_NAME, COLLOCGROUP.COLLOCGROUP_NAME
> FROM COLLOCGROUP WHERE (((COLLOCGROUP.COLLOCGROUP_NAME)=(SELECT
> COLLOCGROUP.COLLOCGROUP_NAME FROM COLLOCGROUP WHERE
> (((COLLOCGROUP.NODE_NAME)=[enter node_name])))));
> using MS Access and ODBC driver, with the same problem
>
> Does someone have an idea of what is happening here ?
> Thanks in advance !
> Cheers.
>
> Arnaud
>
> ************************************************************************
> ******
> Panalpina Management Ltd., Basle, Switzerland, CIT Department
> Viadukstrasse 42, P.O. Box 4002 Basel/CH
> Phone:  +41 (61) 226 11 11, FAX: +41 (61) 226 17 01
> Direct: +41 (61) 226 19 78
> e-mail: arnaud.brion AT panalpina DOT com
> ************************************************************************
> ******