order by without cursor

sanil m · New User Joined: 24 Aug 2007 Posts: 38 Location: chennai

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.

dbzTHEdinosauer · Posted: Thu Nov 22, 2007 6:11 pm

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.

stodolas · Posted: Thu Nov 22, 2007 6:14 pm

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

Srihari Gonugunta · Posted: Thu Nov 22, 2007 6:20 pm

The query you designed, should always return single row. In that case, why do you need an ORDER BY clause.

sanil m · New User Joined: 24 Aug 2007 Posts: 38 Location: chennai

thanx for the reply

@srihari

We are not sure we'll be getting single row or multiple rows.

Srihari Gonugunta · Posted: Thu Nov 22, 2007 6:51 pm

Anil,
Then please make sure query always returns 1 row to avoid -811. Follow Steve's suggestion if it works for you.