|
View previous topic :: View next topic
|
| Author |
Message |
babu_hi
New User
Joined: 11 Apr 2006
Posts: 93
|
|
|
|
I am getting BIND error while updating table thru cursor,i have declared cursor like below,
EXEC SQL DECLARE CNTL_TABLE_CSR2 CURSOR WITH HOLD FOR
SELECT CNTL_TBL_NME,
CNTL_DTE,
CNTL_FLAG
FROM ARW_CNTL_TABLE
WHERE CNTL_FLAG = 'E' OR
CNTL_FLAG = 'H' AND CNTL_DTE = (SELECT MIN(CNTL_DTE) FROM
ARW_CNTL_TABLE WHERE CNTL_FLAG = 'H')
FOR UPDATE OF CNTL_FLAG,CNTL_DTE
END-EXEC.
Please let me know what is wrong in the above cursor declaration? |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
When the bind error occurred, there should be some diagnostic info presented. Please post that info.
There may be a problem with using a subquery in an update cursor. If you search in the db2 sectino of the forum, you may find more of what you are looking for. |
|
| Back to top |
|
|
Prajesh_v_p
Active User
Joined: 24 May 2006
Posts: 133
Location: India
|
|
|
|
Babu,
What's the SQLCODE you are getting? Are u getting the error at bind time or while you try running the program? Please provide more details.
Did u try running the query outside, in a QMF or SPUFI?
Thanks,
Prajesh |
|
| Back to top |
|
|
sri_mf
Active User
Joined: 31 Aug 2006
Posts: 218
Location: India
|
|
|
|
| babu_hi wrote: |
I am getting BIND error while updating table thru cursor,i have declared cursor like below,
SELECT CNTL_TBL_NME,
CNTL_DTE,
CNTL_FLAG
FROM ARW_CNTL_TABLE
WHERE CNTL_FLAG = 'E' OR
CNTL_FLAG = 'H' AND CNTL_DTE = (SELECT MIN(CNTL_DTE) FROM
ARW_CNTL_TABLE WHERE CNTL_FLAG = 'H')
Please let me know what is wrong in the above cursor declaration? |
First check ur query in SPUFI/QMF |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
Checking the sql in qmf or spufi will not help. since you cannot declare a cursor in spufi/qmf. The select, even with the subselect will/could function.
besides, any error by spufi/qmf for the select clause may or may not be germaine to the cursor error found by the bind.
babu_hi,
what is the bind error?
| Code: |
DSNX200I -DQF2 BIND SQL ERROR
USING LFLGD1 AUTHORITY
PLAN=(NOT APPLICABLE)
DBRM=RSR003
STATEMENT=85
SQLCODE=-204
SQLSTATE=42704
TOKENS=LFLGD1.PRT_SAVE
CSECT NAME=DSNXOTL
RDS CODE=-500
|
the above is an example of a bind failure. The SQLCODE would be helpful inorder to provide you an answer |
|
| Back to top |
|
|
babu_hi
New User
Joined: 11 Apr 2006
Posts: 93
|
|
|
|
| i am getting DSNT233I error at the time of bind the program. |
|
| Back to top |
|
|
babu_hi
New User
Joined: 11 Apr 2006
Posts: 93
|
|
|
|
complete informatiuon on BIND ERROR is
DSNX200I - BIND SQL ERROR
USING ARW01D AUTHORITY
PLAN=(NOT APPLICABLE)
DBRM=ARW250
STATEMENT=116
SQLCODE=-118
SQLSTATE=42902
TOKENS=
CSECT NAME=DSNXOGP
RDS CODE=-700
DSNT233I - UNSUCCESSFUL BIND FOR
please let me know what i need to change? |
|
| Back to top |
|
|
sri_mf
Active User
Joined: 31 Aug 2006
Posts: 218
Location: India
|
|
|
|
| dbzTHEdinosauer wrote: |
Checking the sql in qmf or spufi will not help. since you cannot declare a cursor in spufi/qmf. The select, even with the subselect will/could function.
|
Hi dick i have asked OP to check his query(just the exact query)
| Code: |
SELECT CNTL_TBL_NME,
CNTL_DTE,
CNTL_FLAG
FROM ARW_CNTL_TABLE
WHERE CNTL_FLAG = 'E' OR
CNTL_FLAG = 'H' AND CNTL_DTE = (SELECT MIN(CNTL_DTE) FROM
ARW_CNTL_TABLE WHERE CNTL_FLAG = 'H')
|
So that if there are any issues in the query he can fix it there itself and then go for bind..
Usually in our shop we used to test all the queries that we use in the program.Thats why i suggested that.Corrections are welcome. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
THE OBJECT TABLE OR VIEW OF THE DELETE OR UPDATE STATEMENT IS ALSO IDENTIFIED IN A FROM CLAUSE Explanation: The table or view specified as the object of a DELETE or UPDATE statement also appears in the FROM clause of a subselect within the statement. The table or view that is the object of a UPDATE or DELETE cannot also be used to supply the values to be inserted or to qualify the rows to be updated or deleted.
System Action: The statement cannot be executed. No data was updated or deleted.
Programmer Response: The implied function is not supported by DB2. It may be possible to obtain the desired result by creating a temporary copy of the object table or view and addressing the subselect to that copy. Refer to Chapter 5 of DB2 SQL Reference for information about the syntax of SQL statements.
|
As Dick said, the subselect is a problem.
| Code: |
FOR UPDATE OF CNTL_FLAG,CNTL_DTE
|
you are going to update CNTL_FLAG and CNTL_DTE which are both specified in the subselect.
I really don't think your subselect is what you want. I would guess that you want the row that contains the MIN(CNTL_DTE) for a CNTL_TBL_NME that has a CNTL_FLAG = 'H'. What your subselect is returning is the MIN(CNTL_DTE) from all rows containing a CNTL_FLAG = 'H', , without regard to CNTL_TBL_NME.
Not only does the sytanx for a cursor disallow a subselect on columns that are part of an UPDATE clause, your subselect is faulty.
If you would tell us why you want to update the DTE and FLAG columns or what you are trying to do, we maybe able to provide suggestions.
You can not order a CURSOR containing a FOR UPDATE CLAUSE. You may want to change your CURSOR to a FETCH ONLY after you fix your subselect or add an ORDER BY CLAUSE and then do SINGLETON UPDATEs. |
|
| Back to top |
|
|
|
|
