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

[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

[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

[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

[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

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

[Brian Tkatch]

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

With all the bad documentation? :)

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

B.

[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

[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