View previous topic :: View next topic
|
Author |
Message |
Ramanan-R
New User
Joined: 21 Mar 2007 Posts: 66 Location: Chennai, Tamilnadu, India
|
|
|
|
Hi All ,
Below is the cursor...
Code: |
EXEC SQL
DECLARE CUR1 CURSOR FOR
SELECT A.LBS
,A.FDR
,A.FTR
,A.FJN
,B.PBS
,B.PFI
,B.PBN
FROM TABLE1 A INNER JOIN
TABLE2 B
ON (A.FDR = B.FDR AND
A.FTR = B.FTR AND
A.FJN = B.FJN AND
A.FC = B.FC AND
A.BC = B.BC)
WHERE (B.PBS = '1' OR
B.PBS = '3')
FOR UPDATE OF A.LBS
,B.PBS
,B.PFI
,B.PBN
END-EXEC.
|
Is the Cursor syntactically correct?
At some point i would like to update field LBS of Table1 and PBS, PFI and PBN of Table 2 like below... Please advise, if they are correct...
Code: |
EXEC SQL
UPDATE TABLE1 SET
LBS = 'C'
WHERE CURRENT OF CUR1
END-EXEC.
EXEC SQL
UPDATE TABLE2 SET
PBS = :WS-PBS
,PFI = :WS-PFI
,PBN = :WS-PBN
WHERE CURRENT OF CUR1
END-EXEC.
|
Thanks! |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
well, a quick pre-compile would have told you
exactly what the manual would have told you.
no
a join creates a read-only-cursor (on the mainframe) |
|
Back to top |
|
|
Ramanan-R
New User
Joined: 21 Mar 2007 Posts: 66 Location: Chennai, Tamilnadu, India
|
|
|
|
Okay Thanks...
Could you please advise, how to achieve that using SQL... |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
code a singleton update for table1 and a singleton update for table2,
each with a where clause that isolates the exact row for that table (i.e. primary key).
you may have to expand your select in your cursor in order to insure that the required columns are returned in the result set and available for the update where clauses. |
|
Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
Fetch the data from the cursor into host variables then run the update query by specifying the Where conditions. |
|
Back to top |
|
|
Ramanan-R
New User
Joined: 21 Mar 2007 Posts: 66 Location: Chennai, Tamilnadu, India
|
|
|
|
Thanks both for the help...
Could you please advise, if we can give "For Fetch Only" Clause in Cursor declartion? |
|
Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
Back to top |
|
|
Ramanan-R
New User
Joined: 21 Mar 2007 Posts: 66 Location: Chennai, Tamilnadu, India
|
|
|
|
gylbharat,
Thanks for the link...
But the reason i asked for is because in Cursor declaration we are joining two tables but down we want to update few columns that are retrieved through Join operation.
Since we can't use "For Update Of" clause in Cursor using Join. Can we use "For Fetch Only" to retrieve rows in bulk and update required columns using singleton update for each table?
Thanks! |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Ramanan-R wrote: |
gylbharat,
Thanks for the link...
But the reason i asked for is because in Cursor declaration we are joining two tables but down we want to update few columns that are retrieved through Join operation.
Since we can't use "For Update Of" clause in Cursor using Join. Can we use "For Fetch Only" to retrieve rows in bulk and update required columns using singleton update for each table?
Thanks! |
though FOR FETCH ONLY explicitly states that it is a read-only cursor,
a cursor containing a join is not an ambiguous cursor,
it is implicitly read-only.
do you mean rowset retrieval? yes.
the singleton updates are predicated on where clause conditions,
which would be controlled thru use of host-variables.
keep in mind a cobol table item either indexed or subscripted
is not a valid host variable. |
|
Back to top |
|
|
Ramanan-R
New User
Joined: 21 Mar 2007 Posts: 66 Location: Chennai, Tamilnadu, India
|
|
|
|
Thanks Dick,
I thought if we specify "For Fetch Only" in cursor declaration then we can't update columns using singleton update...
Dick, Is there a difference between "For Read Only" and "For Fetch Only"...
Thanks! |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Ramanan-R,
this is not a beginners forum.
members here are expected to at least know the fundamentals and
access and read the manuals.
I don't believe that FOR FETCH ONLY is a valid clause.
FOR READ ONLY is the clause.
not that it matters, you can refer to the manual
- links at top of everypage in this website -
the type of cursor - read-only, update - only affects what sql you can apply against the cursor.
has no effect on the table for sql that is not cursor related. |
|
Back to top |
|
|
|