Group by in subselect

Walter Janißen

Group by in subselect
Hi

Does anybody know, why it is necessary to code ALL or ANY in front of a
subquery, which contains a group by clause even if only 1 row will be
returned?

F.g.

SELECT ...
WHERE ...
GROUP BY PROGNAME, COLLID, VERSION, BIND_TIME
HAVING 2 > ALL (SELECT COUNT(DISTINCT BIND_TIME)
FROM DB2.PLAN_TABLE C
WHERE PROGNAME = &PACKAGE
AND COLLID = 'DB2'
AND C.VERSION = A.VERSION
AND C.BIND_TIME > A.BIND_TIME
GROUP BY PROGNAME, COLLID, VERSION
)

Most of the time it doesn't hurt, but if I want to use such a subquery in a
SET-clause I get a syntax error, because the keyword ALL is not allowed.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Suresh Sane

Re: Group by in subselect
(in response to Walter Janißen)
Walter,

I believe the parser is not smart enough to recognize that only 1 row is
returned from the subselect - it sees a group by and automatically assumes
multiple rows. With only count(*) being selected, this should be easy to
detect but how about cases where you have a group by and local predicates
ensure 1 row - DB2 will not know that. This, as expected, reytuns a -815.

A clumsy alternative is to put a nested-table-expression wrapper around it
(more trouble than worth) like below (it does work):

HAVING 2 >
(SELECT MAX(X.THECOUNT) FROM
(SELECT COUNT(DISTINCT C.BIND_TIME) AS THECOUNT
FROM ?.PLAN_TABLE C
WHERE C.PROGNAME = 'A'
AND C.COLLID = 'B'
AND C.VERSION = A.VERSION
AND C.BIND_TIME > A.BIND_TIME
GROUP BY C.PROGNAME, C.COLLID, C.VERSION
)
AS X
)

Thanks,
Suresh


>From: [login to unmask email]
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: Group by in subselect
>Date: Thu, 4 Dec 2003 05:59:00 -0600
>
>Hi
>
>Does anybody know, why it is necessary to code ALL or ANY in front of a
>subquery, which contains a group by clause even if only 1 row will be
>returned?
>
>F.g.
>
>SELECT ...
> WHERE ...
> GROUP BY PROGNAME, COLLID, VERSION, BIND_TIME
>HAVING 2 > ALL (SELECT COUNT(DISTINCT BIND_TIME)
> FROM DB2.PLAN_TABLE C
> WHERE PROGNAME = &PACKAGE
> AND COLLID = 'DB2'
> AND C.VERSION = A.VERSION
> AND C.BIND_TIME > A.BIND_TIME
> GROUP BY PROGNAME, COLLID, VERSION
> )
>
>Most of the time it doesn't hurt, but if I want to use such a subquery in a
>SET-clause I get a syntax error, because the keyword ALL is not allowed.
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". If you will be out of the office, send the SET
>DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
>can be reached at [login to unmask email] Find out the latest on
>IDUG conferences at http://conferences.idug.org/index.cfm

_________________________________________________________________
Winterize your home with tips from MSN House & Home.
http://special.msn.com/home/warmhome.armx

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

teldb2kals

Re: Group by in subselect
(in response to Suresh Sane)
Hi Walter,

I suppose you have considered the fact that you don't actually need the
group by clause at all in your subselect, considering that the values for
the predicates are known from the outer query, and hence only 1 row will be
returned from the subselect anyway.

Regards,
Kals.

On Thu, 4 Dec 2003 05:59:00 -0600, [login to unmask email] wrote:

>Hi
>
>Does anybody know, why it is necessary to code ALL or ANY in front of a
>subquery, which contains a group by clause even if only 1 row will be
>returned?
>
>F.g.
>
>SELECT ...
> WHERE ...
> GROUP BY PROGNAME, COLLID, VERSION, BIND_TIME
>HAVING 2 > ALL (SELECT COUNT(DISTINCT BIND_TIME)
> FROM DB2.PLAN_TABLE C
> WHERE PROGNAME = &PACKAGE
> AND COLLID = 'DB2'
> AND C.VERSION = A.VERSION
> AND C.BIND_TIME > A.BIND_TIME
> GROUP BY PROGNAME, COLLID, VERSION
> )
>
>Most of the time it doesn't hurt, but if I want to use such a subquery in a
>SET-clause I get a syntax error, because the keyword ALL is not allowed.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Jani&#223;en

Re: Group by in subselect
(in response to teldb2kals)
Suresh

Nice idea, but unfortunately it doesn't work. I just made a test and got
the same syntax error as without the "wrapper".

But actually I want to know, what is the reason, that this keyword is
required? Are there any reasons, which come from set theory?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Elmer J. Valenzuela

Re: Group by in subselect
(in response to Walter Janißen)
Hi,

The Group By option may or may not return more than one result.
For consistency the ALL or ANY keyword is necessary to avoid
syntax problems if you get more than one result from the subquery.

hth../elmer
> Hi
>
> Does anybody know, why it is necessary to code ALL or ANY in front of a
> subquery, which contains a group by clause even if only 1 row will be
> returned?
>
> F.g.
>
> SELECT ...
> WHERE ...
> GROUP BY PROGNAME, COLLID, VERSION, BIND_TIME
> HAVING 2 > ALL (SELECT COUNT(DISTINCT BIND_TIME)
> FROM DB2.PLAN_TABLE C
> WHERE PROGNAME = &PACKAGE
> AND COLLID = 'DB2'
> AND C.VERSION = A.VERSION
> AND C.BIND_TIME > A.BIND_TIME
> GROUP BY PROGNAME, COLLID, VERSION
> )
>
> Most of the time it doesn't hurt, but if I want to use such a subquery in a
> SET-clause I get a syntax error, because the keyword ALL is not allowed.
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
>
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Suresh Sane

Re: Group by in subselect
(in response to Elmer J. Valenzuela)
Walter,

Here is the complete sql that does work (sql runs... I don't have the
data/time to test it).

The "set theory" reason is simply that in DB2's mind group by implies
multiple rows. It does not have the logic to know where it is cleat that
only 1 rows will be returned. (I gave the example of local predicates where
you "Know" only 1 row will be returned, DB2 could not). In any case, here
the example:

SELECT
A.PROGNAME, A.COLLID, A.VERSION, A.BIND_TIME
FROM xxx.PLAN_TABLE A
GROUP BY A.PROGNAME, A.COLLID, A.VERSION, A.BIND_TIME
HAVING 2 >
(SELECT MAX(X.THECOUNT) FROM
(SELECT COUNT(DISTINCT C.BIND_TIME) AS THECOUNT
FROM xxx.PLAN_TABLE C
WHERE C.PROGNAME = 'A'
AND C.COLLID = 'B'
AND C.VERSION = A.VERSION
AND C.BIND_TIME > A.BIND_TIME
GROUP BY C.PROGNAME, C.COLLID, C.VERSION
)
AS X
)

Thanks,
Suresh

>From: [login to unmask email]
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: Group by in subselect
>Date: Fri, 5 Dec 2003 02:45:49 -0600
>
>Suresh
>
>Nice idea, but unfortunately it doesn't work. I just made a test and got
>the same syntax error as without the "wrapper".
>
>But actually I want to know, what is the reason, that this keyword is
>required? Are there any reasons, which come from set theory?
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". If you will be out of the office, send the SET
>DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
>can be reached at [login to unmask email] Find out the latest on
>IDUG conferences at http://conferences.idug.org/index.cfm

_________________________________________________________________
Shop online for kids’ toys by age group, price range, and toy category at
MSN Shopping. No waiting for a clerk to help you! http://shopping.msn.com

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm