Easy Group By Question (I think/hope)

bsimmons01

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

Mar 7 '06 #1

johnl

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

Mar 7 '06 #2

Serge Rielau

bs********@gmail.com wrote:

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

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Mar 7 '06 #3

Knut Stolze

johnl wrote:

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

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

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Mar 8 '06 #4

bsimmons01

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

Mar 8 '06 #5

Brian Tkatch

>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.

Mar 8 '06 #6

Brian Tkatch

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

With all the bad documentation? :)

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

B.

Mar 8 '06 #7

Serge Rielau

bs********@gmail.com wrote:

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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Mar 8 '06 #8

johnl

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

Mar 9 '06 #9