|
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 | |
Share:
|
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 | | | 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 | | |
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 | | |
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 | | |
>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. | | |
>Don't worry. We'll make you blue.
With all the bad documentation? :)
Oh, oh, that world-domination thing...
B. | | | 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 | | |
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 | | This discussion thread is closed Replies have been disabled for this discussion. Similar topics
1 post
views
Thread by Mike King |
last post: by
|
5 posts
views
Thread by John |
last post: by
|
5 posts
views
Thread by KJ |
last post: by
|
7 posts
views
Thread by Tim |
last post: by
|
16 posts
views
Thread by Terry McNamee |
last post: by
|
5 posts
views
Thread by AC |
last post: by
|
5 posts
views
Thread by LedZep |
last post: by
|
1 post
views
Thread by melanieab |
last post: by
|
5 posts
views
Thread by Michael |
last post: by
| | | | | | | | | | |