View previous topic :: View next topic |
Author |
Message |
Sarangadhar Beginner
Joined: 14 Oct 2004 Posts: 130 Topics: 43 Location: virtual village
|
Posted: Mon Nov 19, 2007 3:14 pm Post subject: update failed |
|
|
I have two tables as given below. I am trying to update the tab2 from Tab1 with below query:
UPDATE TAB2 B
SET B.IND = (
SELECT A.IND FROM TAB1 A
WHERE A.SNUM = B.SNUM
)
Code: | error:
SQLCODE = -4700, ERROR: ATTEMPT TO USE NEW FUNCTION BEFORE NEW
FUNCTION MODE
SQLSTATE = 56038 SQLSTATE RETURN CODE
SQLERRP = DSNHSM1 SQL PROCEDURE DETECTING ERROR
SQLERRD = 507 0 0 -1 79 0 SQL DIAGNOSTIC INFORMATION
SQLERRD = X'000001FB' X'00000000' X'00000000' X'FFFFFFFF'
X'0000004F' X'00000000' SQL DIAGNOSTIC INFORMATION |
Tab1:
Code: | SNUM NAME IND
1 AAAA Y
2 BBBB Y
3 CCCC Y
4 DDDD N |
tab2:
_________________ Thanks |
|
Back to top |
|
 |
jsharon1248 Intermediate
Joined: 08 Aug 2007 Posts: 291 Topics: 2 Location: Chicago
|
Posted: Mon Nov 19, 2007 3:36 pm Post subject: |
|
|
Try it without qualifying the columns listed in the SET clause:
Code: |
UPDATE TAB2 B
SET IND = (
SELECT A.IND FROM TAB1 A
WHERE A.SNUM = B.SNUM
)
|
|
|
Back to top |
|
 |
CraigG Intermediate
Joined: 02 May 2007 Posts: 202 Topics: 0 Location: Viginia, USA
|
Posted: Mon Nov 19, 2007 3:37 pm Post subject: Re: update failed |
|
|
Sarangadhar wrote: | I have two tables as given below. I am trying to update the tab2 from Tab1 with below query:
UPDATE TAB2 B
SET B.IND = (
SELECT A.IND FROM TAB1 A
WHERE A.SNUM = B.SNUM
)
Code: | error:
SQLCODE = -4700, ERROR: ATTEMPT TO USE NEW FUNCTION BEFORE NEW
FUNCTION MODE
SQLSTATE = 56038 SQLSTATE RETURN CODE
SQLERRP = DSNHSM1 SQL PROCEDURE DETECTING ERROR
SQLERRD = 507 0 0 -1 79 0 SQL DIAGNOSTIC INFORMATION
SQLERRD = X'000001FB' X'00000000' X'00000000' X'FFFFFFFF'
X'0000004F' X'00000000' SQL DIAGNOSTIC INFORMATION |
Tab1:
Code: | SNUM NAME IND
1 AAAA Y
2 BBBB Y
3 CCCC Y
4 DDDD N |
tab2:
|
Have you tried to lookup that error? I would suspect that it has something to due a recent update to your system. |
|
Back to top |
|
 |
kolusu Site Admin

Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
|
Posted: Mon Nov 19, 2007 4:38 pm Post subject: |
|
|
Sarangadhar,
I think your shop has upgraded to Version 8 of DB2 with new function mode off. Talk to your DBA
Hope this helps...
Cheers _________________ Kolusu
www.linkedin.com/in/kolusu |
|
Back to top |
|
 |
Sarangadhar Beginner
Joined: 14 Oct 2004 Posts: 130 Topics: 43 Location: virtual village
|
Posted: Mon Nov 19, 2007 4:39 pm Post subject: |
|
|
jsharon1248
Thanks.. It's working if i dont qualify the updated column. Any reason why DB2 behaves such a way? _________________ Thanks |
|
Back to top |
|
 |
Sarangadhar Beginner
Joined: 14 Oct 2004 Posts: 130 Topics: 43 Location: virtual village
|
Posted: Mon Nov 19, 2007 4:55 pm Post subject: |
|
|
I did not see the messages from kolusu and craigG when I replied last time.. Maybe I did not refresh the page.
Any idea why it works after removing qualifier in the query as given by jsharon1248. How DB2 confused with qualifiers? _________________ Thanks |
|
Back to top |
|
 |
jsharon1248 Intermediate
Joined: 08 Aug 2007 Posts: 291 Topics: 2 Location: Chicago
|
Posted: Tue Nov 20, 2007 9:05 am Post subject: |
|
|
I'm not sure that DB2 for z/OS syntax ever allows the qualification for the SET columns. In any case, there's no need for it. You can only UPDATE 1 table, so qualifying the columns will not add any clarity to the statement. |
|
Back to top |
|
 |
Sarangadhar Beginner
Joined: 14 Oct 2004 Posts: 130 Topics: 43 Location: virtual village
|
Posted: Tue Nov 20, 2007 1:12 pm Post subject: |
|
|
thanks for clarification, Mr. jsharon1248 _________________ Thanks |
|
Back to top |
|
 |
|
|