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_a mt)
FROM ab.vbudget_r01 a, ab.vabc_rsrc_xr ef 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 8 2908
Use a "nested table expression"
select nte.group_of_co ls, nte.activity_de sc, 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_xr ef b,
WHERE a.gl_yr = 2005 and a.id = b.id ) as nte
GROUP BY nte.group_of_co ls, nte.activity_de sc bs********@gmai l.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_a mt) FROM ab.vbudget_r01 a, ab.vabc_rsrc_xr ef 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_co ls, nte.activity_de sc, 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_xr ef b, WHERE a.gl_yr = 2005 and a.id = b.id ) as nte GROUP BY nte.group_of_co ls, nte.activity_de sc
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_a mt)
FROM ab.vbudget_r01 a, ab.vabc_rsrc_xr ef 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********@gmai l.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(TOT AL_PAY),7,2)
FROM (SELECT EDLEVEL, YEAR(HIREDATE) AS HIREYEAR,
SALARY+BONUS+CO MM AS TOTAL_PAY
FROM EMPLOYEE
WHERE EDLEVEL > 16) AS PAY_LEVEL
GROUP BY EDLEVEL, HIREYEAR
ORDER BY EDLEVEL, HIREYEAR This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Mike King |
last post by:
What RegEx will match the following?
"abc.htm"
'cba.htm'
acb.htm
P.S. I just want the link without the quotes.
|
by: John |
last post by:
Below is my code. It seems that no matter what I do, I cannot get the
following list of places to start at the top of the cell. It is always
centered in the middle of the cell. Is vAlign the correct command for
it to start listing at the top of the cell?
Response.Write "<TABLE BORDER=""1"">"
Response.Write "<TABLE WIDTH=150>"...
|
by: KJ |
last post by:
In an Xml schema: can an element have children which come from another
namespace?
For example, can this be declared in the schema:
<top>
<anotherNamespace:a>
<aThirdNamespace:b>
Hello World
</aThirdNamespace:b>
|
by: Tim |
last post by:
hi all,
I have a table of customers.
I have a table of products they have ordered.
How can I find all customers who have ordered productA and productB at
any time.
It sounds so easy, but I can't quite get my head around it!
|
by: Terry McNamee |
last post by:
return arr.Length == 0 ? null : arr.ToString();
Can anyone tell me what this syntax means?
Does it basically mean if arr.Length is equal to 0 then return null, else
return the item in the array ??
| |
by: AC |
last post by:
Any reason i'm having trouble with:
DataGrid1.Columns(7).ItemStyle.HorizontalAlign =
HorizontalAlign.Right
I'm trying to directly set the column alignment of a
datagrid created at runtime..
AC
|
by: LedZep |
last post by:
What up,
All I need to do is enter a last name in a text box, query a MSAccess
database and display the name with the corresponding columns. This is
no problem, but when there are more than one records with the same
last name, I need to click a command button to display the next record
with that name. I cant find it in any of my books and...
|
by: melanieab |
last post by:
Hi,
If there's a textbox and the text entered is longer than what's visible (the
textbox length), how do you make it so that the beginning chunk of text is
visible (instead of the last part of it)?
Thanks again!!!
Mel
|
by: Michael |
last post by:
Hi all,
I have the following in main:
char output;
function(output);
void function(char *out){
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |