Re: SQL select subquery

Carsten

I agree that subqueries should work. That is what I trying to say in my
email to the ADSM list: SUBQUERIES DO WORK, but the syntax diagram in the
manual is wrong.

The syntax diagram puts the subquery in the wrong place in the select
statement.

The examples with subqueries in the manual are correct and do work.

If you think about it logically:
1.  a subquery is going to return on or more of VALUES from the data
(column from a table), this can usefully be used in the 'where' clause.
2.  the 'from' clause expects TABLE NAMES, not values from the data

Looking at your simple example below:
** select * from select node_name from nodes

What is this supposed to do? If the subquery did work, it would fill in
actual node_names in the place where the select statement expects tables.
That does not make sense!

I quote an example from the manual:
**  select  node_name, count(*) as "Files"
**  from    backups
**  where   class_name='DAILY'
**  and     node_name in (select node_name from nodes where
domain_name='STANDARD')
**  group   by node_name

Here the subquery is filling in node names from table "nodes" into the
WHERE clause. Then the where clause compares that output with the node
names in table "backups".

I hope that helps

Regards
     Geetam


> Date:    Fri, 23 Jul 1999 11:38:46 +0200
> From:    Carsten Moldrup <cmoldrup AT CSC DOT DK>
> Subject: Re: SQL select subquery
> MIME-Version: 1.0
> Content-Type: text/plain; charset=us-ascii
>
> Hi
>
> I believe that this subquery function is a part of the SQL93 specs.
> However, IBM did not implement this function in their DB2 until the
> recently released version 5. Since subqueries now are possible in DB2, i
> believe that it is likely that it should be possible in ADSM as well -
> especially since the manuals state it.
>
> Regards, Carsten Moldrup
>
>
>
>
> > Geetam Van Der Dussen <geetam AT SCOTPROV.CO DOT UK> on 23-07-99 11:04:04
> >
> > Please respond to "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
> >
> > To:   ADSM-L AT VM.MARIST DOT EDU
> > cc:    (bcc: Carsten Moldrup/SCA/CSC)
> > Subject:  Re: SQL select subquery
> >
> >
> >
> >
> > Dear friends
> >
> > I believe that the syntax diagram for the SELECT statement in the
manuals
> > has an error in it:
> > In most flavours of SQL the subquery is part of the 'where' clause, not
> > part of the 'from' clause. The ADSM manual does give some examples with
the
> > subquery in the 'where' clause.
> >
> > IBM/Tivoli: please confirm (and if the syntax diagram is correct, can
you
> > please give a few examples with the subquery in the 'from' clause).
Thanks.
> >
> > The manual also states (correctly as far as I can see) that subqueries
ARE
> > supported (NOT supported in ADSM are: union, intersect, except and
> > correlated subqueries).
> >
> > Regards
> >      Geetam
> >
> >
> > PS. Is anyone doing anything about the digests being screwed up? They
are
> > becoming unreadable.
> >
> >
> > > Date:    Thu, 22 Jul 1999 19:08:31 +0200
> > > From:    Carsten Moldrup <cmoldrup AT CSC DOT DK>
> > > Subject: Re: Antwort: SQL select subquery
> > > MIME-Version: 1.0
> > > Content-Type: text/plain; charset=us-ascii
> > >
> > > Hi
> > >
> > > Thanks for your replies.
> > >
> > > Doing a HELP SELECT in admin cmd line tells that a subquery is valid.
> > > I tried with a little more simple sql - with the same result :
> > >
> > > adsm> select * from select node_name from nodes
> > > ANR2904E Unexpected SQL key word token - 'SELECT'.
> > >
> > >                        |
> > >          ..............V..........................
> > >          select * from select node_name from nodes
> > >
> > > ANS8001I Return code 3.
> > >
> > > adsm>
> > >
> > > ...and.........
> > >
> > > adsm> select * from (select node_name from nodes)
> > > ANR2908E Unexpected SQL punctuation token - '('.
> > >
> > >                        |
> > >          ..............V............................
> > >          select * from (select node_name from nodes)
> > >
> > > ANS8001I Return code 3.
> > >
> > > adsm>
> > >
> > >
> > > IBM, are select subqueries not valid ???
> > >
> > > Regards, Carsten Moldrup
> > >
> >
>


______________________________________________________________________
CONFIDENTIALITY NOTICE:  This message is confidential and for the use only
of the intended recipient.  If you receive the message in error you are not
 entitled to disseminate, copy or use the contents in any way.  In such
circumstances please forward the message back to the sender or contact
Scottish Provident Institution by telephone on 0131 556 9181.

Scottish Provident UK is the marketing name in the UK of The Scottish
Provident Institution, (a mutual life office incorporated by Act of
Parliament registered in Scotland no. Z5).  It is a member of the Scottish
Provident marketing group which also includes Scottish Provident
International Life Assurance Limited (registered in the Isle of Man no.
053002C).  Both companies are regulated by the Personal Investment
Authority.  Scottish Provident Trustees Limited, (registered in Scotland
no. 108046), Scottish Provident Pension Trustees Limited* (registered in
England no. 2404622). Registered Office: 7-11 Melville Street, Edinburgh,
EH3 7YZ. *Registered Office: One Silk Street, London, EC2Y 8HQ.   Head
Office: 6 St Andrew Square, Edinburgh, EH2 2YA.  Telephone 0131 556 9181