Easy Group By Question (I think/hope)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bsimmons01@gmail.com - 07 Mar 2006 21:07 GMT

Hi All,

I'm completely green to DB2, so please pardon my ignorance if this is
an extremely easy question.

I'm converting some queries in our application and it's choking on the
Group-By clause.

Here's the query (part of it):
SELECT a.org_lc || b.oros_rsrc || 'M' as ref_no, b.activity_desc,
sum(a.org_bud_amt)
FROM ab.vbudget_r01 a, ab.vabc_rsrc_xref b,
WHERE a.gl_yr = 2005 and a.id = b.id
GROUP BY ref_no, b.activity_desc

It doesn't like the ref_no in the Group-By and I tried "a.org_lc ||
b.oros_rsrc || 'M' " in there as well, and it didn't like that either.
I tried GROUP 1, 2 and it didn't like that either.

Anybody able to help me out?

Thanks,
Brian

Reply to this Message

johnl - 07 Mar 2006 21:22 GMT

Use a "nested table expression"

select nte.group_of_cols, nte.activity_desc, sum(nte.org_bud_amt) from
(SELECT a.org_lc || b.oros_rsrc || 'M' as group_of_cols ,
b.activity_desc, a.org_bud_amt
FROM ab.vbudget_r01 a, ab.vabc_rsrc_xref b,
WHERE a.gl_yr = 2005 and a.id = b.id ) as nte
GROUP BY nte.group_of_cols, nte.activity_desc

Reply to this Message

Knut Stolze - 08 Mar 2006 07:05 GMT

> Use a "nested table expression"
>
[quoted text clipped - 4 lines]
> WHERE a.gl_yr = 2005 and a.id = b.id ) as nte
> GROUP BY nte.group_of_cols, nte.activity_desc

That's a "subselect" (in case someone wants to look things up in the
manual).

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

johnl - 09 Mar 2006 12:59 GMT

I think it is a "nested table expression". This is copied straight
from the Information Center for Z/OS V7 website...

Nested Table Expressions
A nested table expression is a temporary view where the definition
is nested (defined directly) in the FROM clause of the main query.

The following query uses a nested table expression to find the
average total pay, education level and year of hire, for those with an
education level greater than 16:

SELECT EDLEVEL, HIREYEAR, DECIMAL(AVG(TOTAL_PAY),7,2)
FROM (SELECT EDLEVEL, YEAR(HIREDATE) AS HIREYEAR,
SALARY+BONUS+COMM AS TOTAL_PAY
FROM EMPLOYEE
WHERE EDLEVEL > 16) AS PAY_LEVEL
GROUP BY EDLEVEL, HIREYEAR
ORDER BY EDLEVEL, HIREYEAR

Reply to this Message

Serge Rielau - 07 Mar 2006 21:24 GMT

> Hi All,
>
> I'm completely green to DB2, so please pardon my ignorance if this is
> an extremely easy question.

Don't worry. We'll make you blue.

> Here's the query (part of it):
> SELECT a.org_lc || b.oros_rsrc || 'M' as ref_no, b.activity_desc,
> sum(a.org_bud_amt)
> FROM ab.vbudget_r01 a, ab.vabc_rsrc_xref b,
> WHERE a.gl_yr = 2005 and a.id = b.id
> GROUP BY ref_no, b.activity_desc

Makes sense ref_no is not available within the query.

> It doesn't like the ref_no in the Group-By and I tried "a.org_lc ||
> b.oros_rsrc || 'M' " in there as well, and it didn't like that either.

That's odd. what is the error mesage you get and which version/platform
of DB2 are you on:
Here is what I get (DB2 Viper, but should work since at least DB2 UDB V5
for LUW):
db2 => create table TTT(c1 varchar(10), c2 varchar(20), c3 INT)@
DB20000I The SQL command completed successfully.
db2 => select c1 || c2 || 'M', c3 FROM TTT GROUP BY c1 || c2 || 'M', c3@

1 C3
------------------------------- -----------

0 record(s) selected.

> I tried GROUP 1, 2 and it didn't like that either.

That's correct. Allowing column numbers in ORDER BY was a mistake to
begin with.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

bsimmons01@gmail.com - 08 Mar 2006 14:22 GMT

Hi Serge,

Thanks for your input, we're on:
DB2 UDB v7 for z/OS

The exact error that I'm getting when I run the query with "a.org_lc ||
b.oros_rsrc || 'M' " as the first Group By parameter:
SQLSTATE = 42601
[IBM][CLI Driver][DB2] SQL0104N
An unexpected token "||" was found following "". Expected tokens may
include:
"FOR WITH FETCH ORDER UNION EXCEPT QUERYNO OPTIMIZE ". SQLSTATE=42601

Any thoughts?

Thanks again,
Brian

Reply to this Message

Serge Rielau - 08 Mar 2006 15:13 GMT

> Hi Serge,
>
> Thanks for your input, we're on:
> DB2 UDB v7 for z/OS

I see... There is a reason why DB2 V8 for zOS was such a big release.
Push the expression into a subquery:
SELECT x, y FROM (SELECT c(1 + c2) as x, y FROM T) AS S GROUP BY x, y

That should do it.
Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Brian Tkatch - 08 Mar 2006 15:01 GMT

>Don't worry. We'll make you blue.

With all the bad documentation? :)

Oh, oh, that world-domination thing...

B.

Reply to this Message

Brian Tkatch - 08 Mar 2006 15:00 GMT

>and I tried "a.org_lc || b.oros_rsrc || 'M' "

No reason for the M, since it's a constant, and no reason for the ||
since GROUPing with it or without it, is the same thing.

Try:

SELECT a.org_lc || b.oros_rsrc || 'M' as ref_no, b.activity_desc,
sum(a.org_bud_amt)
FROM ab.vbudget_r01 a, ab.vabc_rsrc_xref b,
WHERE a.gl_yr = 2005 and a.id = b.id
GROUP BY a.org_lc, b.oros_rsrc, b.activity_desc

B.

Reply to this Message