Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
how to code sql query if need to sum only first 50 rows

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sandhya rani

New User


Joined: 05 Dec 2006
Posts: 22
Location: hyderabad

PostPosted: Wed Dec 06, 2006 12:12 am    Post subject: how to code sql query if need to sum only first 50 rows
Reply with quote

Hi,

Please let me know how do i sum the amount for first 50 retrieved rows.

if my query results in 100(more then 50) but i need to sum the amount of only first 50 rows fetched.Please suggest accordingly.

My query is related to mainframe technogy in Cobol-db2 program
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Wed Dec 06, 2006 10:15 am    Post subject:
Reply with quote

Hi there,

R ur program already have a cursor on the table for some processing?
Back to top
View user's profile Send private message
mubashir.surury

New User


Joined: 29 Sep 2005
Posts: 23
Location: Pune

PostPosted: Wed Dec 06, 2006 12:36 pm    Post subject: Re: how to code sql query if need to sum only first 50 rows
Reply with quote

Hi Sandhya,

You can write your query to get the SUm of the required rows and at the end of the just give 'FETCH FIRST 50 ROWS ONLY'.if there is some sort criteria for the same query you might want order you rowset as per that one.

Eg......

EXEC SQL
SELECT <your columns you need to fetch>
FROM <TABLE(S)>
WHERE <your selection criteria>
ORDER BY <approp. columns>
FETCH FIRST 50 ROWS ONLY
END SQL

Correct me if I am wrong

Regards,
Mubashir
Back to top
View user's profile Send private message
rambabu
Currently Banned

New User


Joined: 18 Apr 2005
Posts: 67

PostPosted: Wed Dec 06, 2006 2:37 pm    Post subject: Re: how to code sql query if need to sum only first 50 rows
Reply with quote

hi,

SELECT <your columns you need to fetch> ,sum (amount)
FROM <TABLE(S)>
FETCH FIRST 50 ROWS ONLY
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Wed Dec 06, 2006 3:13 pm    Post subject:
Reply with quote

Hi There,

Following query

Code:
SELECT <your columns you need to fetch> ,sum (amount)
FROM <TABLE(S)>
FETCH FIRST 50 ROWS ONLY


It will give you

Code:
SQLCODE = -122,ERROR:  A SELECT STATEMENT WITH NO GROUP BY CLAUSE CONTAINS A COLUMN NAME AND A COLUMN FUNCTION IN THE SELECT CLAUSE OR A COLUMN NAME IS CONTAINED IN THE SELECT CLAUSE BUT NOT IN THE GROUP BY CLAUSE
Back to top
View user's profile Send private message
mubashir.surury

New User


Joined: 29 Sep 2005
Posts: 23
Location: Pune

PostPosted: Wed Dec 06, 2006 3:23 pm    Post subject:
Reply with quote

Yes Ekta,

I agree But the point here is to sum up the first 50 rows.........But any way yes we need to use GROUP BY Clause while using a COLUMN FUNCTION such as SUM.........


Regards,
Mubashir
Back to top
View user's profile Send private message
sandhya rani

New User


Joined: 05 Dec 2006
Posts: 22
Location: hyderabad

PostPosted: Wed Dec 06, 2006 7:39 pm    Post subject: Re: how to code sql query if need to sum only first 50 rows
Reply with quote

HI mubashir,

Ya actually i tried the same query without using order by and it has fetched only 50 rows.

inorder to sum up the amount i have given the query as follows

EXEC SQL
SELECT sum(amount-field-name)
FROM <TABLE(S)>
WHERE <your selection criteria>
FETCH FIRST 50 ROWS ONLY
END SQL

but this query gave the sum of all the rows retrieved but it has not given the sum of only first 50 rows.

As per my understanding fetch only works on fetchings of the rows but does not work on functions like SUmming etc...so i just wanted to know if there is any easier query which gives solution for my query.

anyways iam able to achieve this using cursors but still if there is any easier methos i jus wanted to know.Please let me know if u have any updates on the same.Thanks


Thanks & Regards
Sandhya Rani
mubashir.surury wrote:
Hi Sandhya,

You can write your query to get the SUm of the required rows and at the end of the just give 'FETCH FIRST 50 ROWS ONLY'.if there is some sort criteria for the same query you might want order you rowset as per that one.

Eg......

EXEC SQL
SELECT <your columns you need to fetch>
FROM <TABLE(S)>
WHERE <your selection criteria>
ORDER BY <approp. columns>
FETCH FIRST 50 ROWS ONLY
END SQL

Correct me if I am wrong

Regards,
Mubashir
Quote:
Back to top
View user's profile Send private message
sandhya rani

New User


Joined: 05 Dec 2006
Posts: 22
Location: hyderabad

PostPosted: Wed Dec 06, 2006 7:42 pm    Post subject: Re: how to code sql query if need to sum only first 50 rows
Reply with quote

Hi ,

I have posted the reply please look into that and let me know if you have any updates on the same.I have already tried the same but still it was summing the amount for all the retrieved rows it is not restricting to 50 rows.

Thanks & regards
Sandhya Rani



rambabu wrote:
hi,

SELECT <your columns you need to fetch> ,sum (amount)
FROM <TABLE(S)>
FETCH FIRST 50 ROWS ONLY
Back to top
View user's profile Send private message
MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA

PostPosted: Wed Dec 06, 2006 9:27 pm    Post subject: Re: how to code sql query if need to sum only first 50 rows
Reply with quote

This is not a straight forward way but consider this:

Create Global temporary table, insert 50 rows you want SUM to be done on, then use SUM on temp. table.
Back to top
View user's profile Send private message
Alan Voss

New User


Joined: 29 Nov 2006
Posts: 32
Location: Jacksonville, FL

PostPosted: Wed Dec 06, 2006 10:52 pm    Post subject:
Reply with quote

How about:
select sum(a.amount) as total
from (select amount
from table1
fetch first 50 rows only) a;
Back to top
View user's profile Send private message
sandhya rani

New User


Joined: 05 Dec 2006
Posts: 22
Location: hyderabad

PostPosted: Thu Dec 07, 2006 9:55 am    Post subject:
Reply with quote

Hi,

I have tried the query as follows

select sum(a.amount) as total
from (select amount
from table1
where fields1 = '12345'
fetch first 50 rows only)

but it is giving error as
QUERY MESSAGES:
SQL error at or before FETCH (line 6, position 9). ---i think it does not accept fetch...

please correct me if there is anything wrong in the query that i tried.

Thanks & regards
Sandhya Rani


Alan Voss wrote:
How about:
select sum(a.amount) as total
from (select amount
from table1
fetch first 50 rows only) a;
Back to top
View user's profile Send private message
Alan Voss

New User


Joined: 29 Nov 2006
Posts: 32
Location: Jacksonville, FL

PostPosted: Thu Dec 07, 2006 6:34 pm    Post subject:
Reply with quote

I just tried it again and it worked for me. Then, I tried it on z/OS DB2 and I got a message similar (but different) from yours. Apparently, that is one different between DB2 on AIX and DB2 on z/OS.

Sorry, but it doesn't look like I can help you.
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Wed Dec 27, 2006 7:11 am    Post subject: Re: how to code sql query if need to sum only first 50 rows
Reply with quote

Sandhya Rani,

Did you ever get a resolution to your question?

Does this have to be in a single SQL?

What version of DB2 are you using. V8 and V9 appear to have some enhancements in this area.
Back to top
View user's profile Send private message
sandhya rani

New User


Joined: 05 Dec 2006
Posts: 22
Location: hyderabad

PostPosted: Wed Dec 27, 2006 9:44 am    Post subject: Re: how to code sql query if need to sum only first 50 rows
Reply with quote

Hi David,

I have not yet got the solution meanwhile iam procedding using cursors for the same.But iam also searching for a better solution.Please do share with me if you have any information regarding the same.BD2 version that we are using are as follows

DB2 Version: 7.1
FADB2 Version: 4.8.1.13
SQLP Version: 04.08.01
FM Version: R2.1

Thanks & regards
Sandhya Rani







DavidatK wrote:
Sandhya Rani,

Did you ever get a resolution to your question?

Does this have to be in a single SQL?

What version of DB2 are you using. V8 and V9 appear to have some enhancements in this area.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
This topic is locked: you cannot edit posts or make replies. how to code for GDG in Rexx cvnlynn CLIST & REXX 13 Wed Aug 09, 2017 9:13 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us