Expression support in Group by clause

Jessen Michael

Expression support in Group by clause
We are on DB2 V5 OS/390 (almost on V6!).

Does anyone know when expressions in a GROUP BY statement will be supported
on OS/390? V7?

Thanks!
Mike

SELECT UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE,
OUTSTANDING_BALANCE
FROM SIDBP01.RDXNOTEA
WHERE UNIT_CODE = '01'
GROUP BY UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE

Statement highlighted in RED is considered a complex object and isn't
currently supported by DB2, but is other RDBMS' like Oracle.



John Hardy

Re: Expression support in Group by clause
(in response to Rob Crane)
You can already (on V5) do this with an NTE:

SELECT UNACSE, SUM(OUTSTANDING_BALANCE)

FROM (SELECT UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE AS UNACSE,
OUTSTANDING_BALANCE
FROM SIDBP01.RDXNOTEA
WHERE UNIT_CODE = '01') AS UNACSE_NTE

GROUP BY UNACSE


On Thu, 11 Jan 2001 09:56:07 -0600, Jessen Michael
<[login to unmask email]> wrote:

>We are on DB2 V5 OS/390 (almost on V6!).
>
>Does anyone know when expressions in a GROUP BY statement will be supported
>on OS/390? V7?
>
>Thanks!
>Mike
>
>SELECT UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE,
> OUTSTANDING_BALANCE
>FROM SIDBP01.RDXNOTEA
>WHERE UNIT_CODE = '01'
>GROUP BY UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE
>
>Statement highlighted in RED is considered a complex object and isn't
>currently supported by DB2, but is other RDBMS' like Oracle.
>
>
>





Rob Crane

Re: Expression support in Group by clause
(in response to Jessen Michael)
Don't have an answer on support of concatenation in group by, maybe I am
still half asleep but wouldn't the following group by statements get you
the same result?

GROUP BY UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE

GROUP BY UNTI_CODE, ACCOUNT_NUMBER, SEQUENCE_CODE

Also if this is really something that you need to treat as one chunk
would it be worth adding a column to your table and indexing it, having
this new column containing the concatenated information?


Jessen Michael wrote:
>
> We are on DB2 V5 OS/390 (almost on V6!).
>
> Does anyone know when expressions in a GROUP BY statement will be supported
> on OS/390? V7?
>
> Thanks!
> Mike
>
> SELECT UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE,
> OUTSTANDING_BALANCE
> FROM SIDBP01.RDXNOTEA
> WHERE UNIT_CODE = '01'
> GROUP BY UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE
>
> Statement highlighted in RED is considered a complex object and isn't
> currently supported by DB2, but is other RDBMS' like Oracle.
>
>
>



Smike Toppins

Re: Expression support in Group by clause
(in response to John Hardy)
No, there's a big difference. The first GROUP BY gives you one break point;
the second one gives you three.


SMike Toppins
Great-West Life
[login to unmask email]
(303) 737-5094


> ----------
> From: Rob Crane[SMTP:[login to unmask email]
> Reply To: DB2 Data Base Discussion List
> Sent: Thursday, January 11, 2001 9:38 AM
> To: [login to unmask email]
> Subject: Re: Expression support in Group by clause
>
> Don't have an answer on support of concatenation in group by, maybe I am
> still half asleep but wouldn't the following group by statements get you
> the same result?
>
> GROUP BY UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE
>
> GROUP BY UNTI_CODE, ACCOUNT_NUMBER, SEQUENCE_CODE
>
> Also if this is really something that you need to treat as one chunk
> would it be worth adding a column to your table and indexing it, having
> this new column containing the concatenated information?
>
>
> Jessen Michael wrote:
> >
> > We are on DB2 V5 OS/390 (almost on V6!).
> >
> > Does anyone know when expressions in a GROUP BY statement will be
> supported
> > on OS/390? V7?
> >
> > Thanks!
> > Mike
> >
> > SELECT UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE,
> > OUTSTANDING_BALANCE
> > FROM SIDBP01.RDXNOTEA
> > WHERE UNIT_CODE = '01'
> > GROUP BY UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE
> >
> > Statement highlighted in RED is considered a complex object and isn't
> > currently supported by DB2, but is other RDBMS' like Oracle.
> >
> >
> >
>
>
>
>
>
>
>
>



Susan Lawson

Re: Expression support in Group by clause
(in response to Smike Toppins)
Mike
Try the following.....


SELECT NEWCOL, OB FROM
(SELECT UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE AS
NEWCOL, OUTSTANDING_BALANCE AS OB
FROM SIDBP01.RDXNOTEA
WHERE UNIT_CODE = '01')
GROUP BY NEWCOL

Regards,
Susan Lawson
Yevich, Lawson and Associates, Inc (YL&A)
www.ylassoc.com


--- "Toppins, Smike" <[login to unmask email]> wrote:
> No, there's a big difference. The first GROUP BY
> gives you one break point;
> the second one gives you three.
>
>
> SMike Toppins
> Great-West Life
> [login to unmask email]
> (303) 737-5094
>
>
> > ----------
> > From: Rob
> Crane[SMTP:[login to unmask email]
> > Reply To: DB2 Data Base Discussion List
> > Sent: Thursday, January 11, 2001 9:38 AM
> > To: [login to unmask email]
> > Subject: Re: Expression support in Group by
> clause
> >
> > Don't have an answer on support of concatenation
> in group by, maybe I am
> > still half asleep but wouldn't the following group
> by statements get you
> > the same result?
> >
> > GROUP BY UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE
> >
> > GROUP BY UNTI_CODE, ACCOUNT_NUMBER, SEQUENCE_CODE
> >
> > Also if this is really something that you need to
> treat as one chunk
> > would it be worth adding a column to your table
> and indexing it, having
> > this new column containing the concatenated
> information?
> >
> >
> > Jessen Michael wrote:
> > >
> > > We are on DB2 V5 OS/390 (almost on V6!).
> > >
> > > Does anyone know when expressions in a GROUP BY
> statement will be
> > supported
> > > on OS/390? V7?
> > >
> > > Thanks!
> > > Mike
> > >
> > > SELECT UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE,
> > > OUTSTANDING_BALANCE
> > > FROM SIDBP01.RDXNOTEA
> > > WHERE UNIT_CODE = '01'
> > > GROUP BY
> UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE
> > >
> > > Statement highlighted in RED is considered a
> complex object and isn't
> > > currently supported by DB2, but is other RDBMS'
> like Oracle.
> > >
> > >
> > > To change your subscription options or to cancel
> your subscription visit
> > the DB2-L webpage at http://www.ryci.com/db2-l.
> The owners of the list can
> >
> >
> >
> > To change your subscription options or to cancel
> your subscription visit
> > the DB2-L webpage at http://www.ryci.com/db2-l.
> The owners of the list can
> >
> >
>
>
> To change your subscription options or to cancel
> your subscription visit the DB2-L webpage at
> http://www.ryci.com/db2-l. The owners of the list
> can
>
>


=====
Regards,
Susan Lawson
Yevich, Lawson and Associates, Inc
www.ylassoc.com - DB2 Performance Journal

__________________________________________________
Do You Yahoo!?
Yahoo! Photos - Share your holiday photos online!
http://photos.yahoo.com/



Terry Purcell

Re: Expression support in Group by clause
(in response to Susan Lawson)
Michael,

DB2 V7 for OS/390 does not allow expressions in the GROUP BY, although
non-OS390 DB2 does...so I'm assuming that V8 should allow this.

Another alternative (although there was nothing wrong with the previous
solutions):
SELECT UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE,
SUM(OUTSTANDING_BALANCE)
FROM SIDBP01.RDXNOTEA
WHERE UNIT_CODE = '01'
GROUP BY UNIT_CODE, ACCOUNT_NUMBER, SEQUENCE_CODE

As for the issue about breakpoints, that's true if you wish to run this thru
QMF and set your breakpoints (or your own program). Otherwise it should not
be an issue.

Regards
Terry Purcell
Yevich Lawson & Associates
[login to unmask email]
WWW.YLASSOC.COM
WWW.DB2-SYMPOSIUM.COM

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Susan Lawson
Sent: Thursday, January 11, 2001 10:53 AM
To: [login to unmask email]
Subject: Re: Expression support in Group by clause


Mike
Try the following.....


SELECT NEWCOL, OB FROM
(SELECT UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE AS
NEWCOL, OUTSTANDING_BALANCE AS OB
FROM SIDBP01.RDXNOTEA
WHERE UNIT_CODE = '01')
GROUP BY NEWCOL

Regards,
Susan Lawson
Yevich, Lawson and Associates, Inc (YL&A)
www.ylassoc.com


--- "Toppins, Smike" <[login to unmask email]> wrote:
> No, there's a big difference. The first GROUP BY
> gives you one break point;
> the second one gives you three.
>
>
> SMike Toppins
> Great-West Life
> [login to unmask email]
> (303) 737-5094
>
>
> > ----------
> > From: Rob
> Crane[SMTP:[login to unmask email]
> > Reply To: DB2 Data Base Discussion List
> > Sent: Thursday, January 11, 2001 9:38 AM
> > To: [login to unmask email]
> > Subject: Re: Expression support in Group by
> clause
> >
> > Don't have an answer on support of concatenation
> in group by, maybe I am
> > still half asleep but wouldn't the following group
> by statements get you
> > the same result?
> >
> > GROUP BY UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE
> >
> > GROUP BY UNTI_CODE, ACCOUNT_NUMBER, SEQUENCE_CODE
> >
> > Also if this is really something that you need to
> treat as one chunk
> > would it be worth adding a column to your table
> and indexing it, having
> > this new column containing the concatenated
> information?
> >
> >
> > Jessen Michael wrote:
> > >
> > > We are on DB2 V5 OS/390 (almost on V6!).
> > >
> > > Does anyone know when expressions in a GROUP BY
> statement will be
> > supported
> > > on OS/390? V7?
> > >
> > > Thanks!
> > > Mike
> > >
> > > SELECT UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE,
> > > OUTSTANDING_BALANCE
> > > FROM SIDBP01.RDXNOTEA
> > > WHERE UNIT_CODE = '01'
> > > GROUP BY
> UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE
> > >
> > > Statement highlighted in RED is considered a
> complex object and isn't
> > > currently supported by DB2, but is other RDBMS'
> like Oracle.
> > >
> > >
> > > To change your subscription options or to cancel
> your subscription visit
> > the DB2-L webpage at http://www.ryci.com/db2-l.
> The owners of the list can
> >
> >
> >
> > To change your subscription options or to cancel
> your subscription visit
> > the DB2-L webpage at http://www.ryci.com/db2-l.
> The owners of the list can
> >
> >
>
>
> To change your subscription options or to cancel
> your subscription visit the DB2-L webpage at
> http://www.ryci.com/db2-l. The owners of the list
> can
>
>


=====
Regards,
Susan Lawson
Yevich, Lawson and Associates, Inc
www.ylassoc.com - DB2 Performance Journal

__________________________________________________
Do You Yahoo!?
Yahoo! Photos - Share your holiday photos online!
http://photos.yahoo.com/








Jessen Michael

Re: Expression support in Group by clause
(in response to Terry Purcell)
Unfortunately, we are using a Business Objects front end and I don't have
the luxury of being able to recode the SQL... well, I do, but this would
kinda take the 'ad' out of ad hoc :-)

Unfortunately, Creating a column for the expression is the answer I was
expecting, just not wanting to hear.

Thanks!
Mike



> -----Original Message-----
> From: Susan Lawson [SMTP:[login to unmask email]
> Sent: Thursday, January 11, 2001 10:53 AM
> To: [login to unmask email]
> Subject: Re: Expression support in Group by clause
>
> Mike
> Try the following.....
>
>
> SELECT NEWCOL, OB FROM
> (SELECT UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE AS
> NEWCOL, OUTSTANDING_BALANCE AS OB
> FROM SIDBP01.RDXNOTEA
> WHERE UNIT_CODE = '01')
> GROUP BY NEWCOL
>
> Regards,
> Susan Lawson
> Yevich, Lawson and Associates, Inc (YL&A)
> www.ylassoc.com
>
>
> --- "Toppins, Smike" <[login to unmask email]> wrote:
> > No, there's a big difference. The first GROUP BY
> > gives you one break point;
> > the second one gives you three.
> >
> >
> > SMike Toppins
> > Great-West Life
> > [login to unmask email]
> > (303) 737-5094
> >
> >
> > > ----------
> > > From: Rob
> > Crane[SMTP:[login to unmask email]
> > > Reply To: DB2 Data Base Discussion List
> > > Sent: Thursday, January 11, 2001 9:38 AM
> > > To: [login to unmask email]
> > > Subject: Re: Expression support in Group by
> > clause
> > >
> > > Don't have an answer on support of concatenation
> > in group by, maybe I am
> > > still half asleep but wouldn't the following group
> > by statements get you
> > > the same result?
> > >
> > > GROUP BY UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE
> > >
> > > GROUP BY UNTI_CODE, ACCOUNT_NUMBER, SEQUENCE_CODE
> > >
> > > Also if this is really something that you need to
> > treat as one chunk
> > > would it be worth adding a column to your table
> > and indexing it, having
> > > this new column containing the concatenated
> > information?
> > >
> > >
> > > Jessen Michael wrote:
> > > >
> > > > We are on DB2 V5 OS/390 (almost on V6!).
> > > >
> > > > Does anyone know when expressions in a GROUP BY
> > statement will be
> > > supported
> > > > on OS/390? V7?
> > > >
> > > > Thanks!
> > > > Mike
> > > >
> > > > SELECT UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE,
> > > > OUTSTANDING_BALANCE
> > > > FROM SIDBP01.RDXNOTEA
> > > > WHERE UNIT_CODE = '01'
> > > > GROUP BY
> > UNIT_CODE||ACCOUNT_NUMBER||SEQUENCE_CODE
> > > >
> > > > Statement highlighted in RED is considered a
> > complex object and isn't
> > > > currently supported by DB2, but is other RDBMS'
> > like Oracle.
> > > >
> > > >
> > > > To change your subscription options or to cancel
> > your subscription visit
> > > the DB2-L webpage at http://www.ryci.com/db2-l.
> > The owners of the list can
> > >
> > >
> > >
> > > To change your subscription options or to cancel
> > your subscription visit
> > > the DB2-L webpage at http://www.ryci.com/db2-l.
> > The owners of the list can
> > >
> > >
> >
> >
> > To change your subscription options or to cancel
> > your subscription visit the DB2-L webpage at
> > http://www.ryci.com/db2-l. The owners of the list
> > can
> >
> >
>
>
> =====
> Regards,
> Susan Lawson
> Yevich, Lawson and Associates, Inc
> www.ylassoc.com - DB2 Performance Journal
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Photos - Share your holiday photos online!
> http://photos.yahoo.com/
>
>
>
>
>