By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,590 Members | 2,174 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,590 IT Pros & Developers. It's quick & easy.

Easy Group By Question (I think/hope)

P: n/a
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
Share this Question
Share on Google+
8 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
>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

P: n/a
>Don't worry. We'll make you blue.

With all the bad documentation? :)

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

B.

Mar 8 '06 #7

P: n/a
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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.