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
 

 

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

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

New User


Joined: 23 May 2007
Posts: 3
Location: Chennai

PostPosted: Fri Jun 08, 2007 11:05 pm    Post subject: How to get data to update in a cursor in a particular Order?
Reply with quote

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
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jun 08, 2007 11:24 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
@Vikramjeet

New User


Joined: 23 May 2007
Posts: 3
Location: Chennai

PostPosted: Fri Jun 08, 2007 11:32 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Jun 09, 2007 12:01 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
@Vikramjeet

New User


Joined: 23 May 2007
Posts: 3
Location: Chennai

PostPosted: Sat Jun 09, 2007 2:55 pm    Post subject:
Reply with quote

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

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. icon_redface.gif

Thanks for ur patience.

Vinnie
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Jun 09, 2007 8:38 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Santoshdorge

New User


Joined: 27 Jun 2006
Posts: 48
Location: Pune

PostPosted: Wed Jun 13, 2007 12:51 pm    Post subject:
Reply with quote

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


thanks,
Santosh
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 NDM syntax checking without actually ... GAPX1 All Other Mainframe Topics 0 Wed Jun 07, 2017 2:36 am
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm


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