How to get data to update in a cursor in a particular Order?

@Vikramjeet · New User Joined: 23 May 2007 Posts: 3 Location: Chennai

Hi,

I have a requirement to update ....where current of in a cursor. And i also want the data to be ordered by a particular Column? I used Order By and For update but the precompiler kicked me ...saying.... "ORDER BY" cannot be used "FOR UPDATE OF"?

Vinnie

dick scherrer · Posted: Fri Jun 08, 2007 11:24 pm

Hello,

When you use a cursor, the ORDER is specified in the SELECT within the cursor definition.

What purpose would another order of update serve?

If you post what your requirement is (what you are trying to accomplish), we may be able to offer suggestions.

@Vikramjeet · New User Joined: 23 May 2007 Posts: 3 Location: Chennai

I am writing the sample req I want to get:

SELECT x,y,z,
FROM abc
WHERE x = '123'
ORDER BY 1,2
FOR UPDATE OF z.

I want to select x,y,z from abc in the order of x,y and for update of z.

I have written the query in the given way but precompiler gives me error:
DSNH126I E DSNHSM1 LINE 478 COL 18 "ORDER BY" CANNOT BE USED WITH A "FOR UPDATE OF" CLAUSE.

Can I achieve the same result without handling it in the program?

Please let me know if I am not clear still.

dick scherrer · Posted: Sat Jun 09, 2007 12:01 am

Hello,

Unfortunately, i'm still not clear.

As the system showed you, you cannot use ORDER BY in the UPDATE. I'm still not clear what this would accomplish even if you could.

Please post a few rows of data (and the named columns from your post) that would be fetched by the cursor and how you want the update to happen. Seeing the desired result may help me to understand.

@Vikramjeet · New User Joined: 23 May 2007 Posts: 3 Location: Chennai

I am really sory to be very unclear. I am gonna try my best this time.

My table is associate_application_details.... it has columns -
Department
associate name
Status of Associate application

and few other columns.

I want these three to be selected.
and then on the basis of values in associate name and department I have to update the status of application.

I also have to write a report of the application for which the status was not updated. This report should page break by Department and then by associate name(one associate can have more than one applications).

For efficiency I want the update to be done thru UPDATE ...where current of. So I have to add the FOR UPDATE OF Clause at the end of the declaration of cursor.

And for getting the input data ordered by Department and then associate name, I will have to use order by.
[if I do the desrired select on the data I dont get the output in order....... department ....then associate name]

But now as u also said that these two ORDER BY and FOR UPDATE OF canont be used together .... I am wondering how I can get the desired results.

the input data selected without orderby clause in declaration of cursor:

Deptt: associate name status
10 Mike ' '
05 Anna ' '
10 Mike ' '
04 XYZ ' '

I have to write the report for all these records but to create the report I will not be able to give a page break at department.

But if I have the data in this fashion:

Deptt: associate name status
04 XYZ ' '
05 Anna ' '
10 Mike ' '
10 Mike ' '

I can easily page break the report by the Deptt by comparing to the previous deptt on every fetch.

Also FYI none of the columns selected are in the where clause of the cursor.

Please let me know if I am still not clear.

Thanks for ur patience.

Vinnie

dick scherrer · Posted: Sat Jun 09, 2007 8:38 pm

Hello,

Are you familiar with the internal sort? Using th input procedure/output procedure of the sort, you can do what you need.

You could do the update as you have it and then "release" the report info to the report as you go thru the updates. When the updates are done, you would be done in the input procedure.

The output procedure would "return" the records from the sort and produce the report. To get the sequence you need, the sort would arrange the selected data in the sequence you need.

Please le tme know if i can provide any other info. I'm away from my main computers, but will check as i can this weekend.

Santoshdorge · New User Joined: 27 Jun 2006 Posts: 48 Location: Pune

Hi,
Use the intensive cursor declaration and write update query separately using the selected values in the cursor for particular row.

thanks,
Santosh