Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

order by without cursor

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

New User


Joined: 24 Aug 2007
Posts: 38
Location: chennai

PostPosted: Thu Nov 22, 2007 5:08 pm    Post subject: order by without cursor
Reply with quote

Hi,

I want to get the minimum of the product_num from the below query,

SELECT PRODUCT_NUM,
FORM_TYPE,
CLAIMFACTS_IND,
INTO :ZN-PRODUCT-NUM,
:ZN-FORM-TYPE,
:ZN-CLAIMFACTS-IND,
FROM RATE_NETWORK
WHERE CARRIER = :ZN-CARRIER
AND STATE = :ZN-STATE
AND COUNTY = :ZN-COUNTY
AND PROD_TYPE = :ZN-PROD-TYPE
AND PROD_LINE = :ZN-PROD-LINE
ORDER BY PRODUCT_NUM

I am getting the error that "order by" cannot be used.

But when i am using min(product_num) and the group by clause the query is working, can someone please tell me why order by doesn't work without cursor.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Thu Nov 22, 2007 6:11 pm    Post subject:
Reply with quote

need to have vsn 8 or better and use the fetch first row only clause for the order by to work.

if you are on vsn 7 or lower, you are out of luck.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Nov 22, 2007 6:14 pm    Post subject:
Reply with quote

Because if you have a scalar function MIN you are returning only one row. However once you take away that scalar, the return set is more than one row and COBOL can't handle that without a cursor.

To get the minimum, you don't even need to use the group by, you could get away with
Code:

SELECT MIN(Product_num)
FROM RATE_NETWORK
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Thu Nov 22, 2007 6:20 pm    Post subject:
Reply with quote

The query you designed, should always return single row. In that case, why do you need an ORDER BY clause.
Back to top
View user's profile Send private message
sanil m

New User


Joined: 24 Aug 2007
Posts: 38
Location: chennai

PostPosted: Thu Nov 22, 2007 6:34 pm    Post subject:
Reply with quote

thanx for the reply

@srihari

We are not sure we'll be getting single row or multiple rows.
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Thu Nov 22, 2007 6:51 pm    Post subject:
Reply with quote

Anil,
Then please make sure query always returns 1 row to avoid -811. Follow Steve's suggestion if it works for you.
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 Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm
No new posts Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am
No new posts Release of Adabas Cursor mohitsethi All Other Mainframe Topics 1 Mon Feb 06, 2017 8:36 pm
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us