|
View previous topic :: View next topic
|
| Author |
Message |
dirceu.alves
New User
Joined: 03 Aug 2005
Posts: 17
|
|
|
|
Hi there,
I need a solution via SQL (not COBOL) which allows me to update an specific position on a varchar column.
I thought it should be something like this:
UPDATE COL_1(115:1) = 'X'
FROM TABLEXXX
WHERE COL_1(115:1) = 'A'
The example above reflects exactly what I need to do. Changing the 115th position of COL_1 column from 'A' to 'X'.
Is that possible?
Thanks again for your help.... |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
What happens when you try it in a test environment?
Requirements that are so simple to test should be tested and if results that are unexpected or not understood happen that should be posted.
When you post what you try and what happens whether it be an error code, undesired results, or no results, someone here will be able to clarify. |
|
| Back to top |
|
|
dirceu.alves
New User
Joined: 03 Aug 2005
Posts: 17
|
|
|
|
Ok... so here is the command
UPDATE TABLE.TABLE
SET SUBSTR(ABUR_RC_TX,115,1) = '5'
WHERE SVC_CD = '00'
AND MPS_ERR_ID = '230'
AND MSG_RC_ID = '010250'
AND SUBSTR(ABUR_RC_TX,115,1) = '2';
I want to change the content of 115th position of the varchar column ABUR_RC_TX but here is the error I'm getting...
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: =
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 83 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF'
X'00000053' X'00000000' SQL DIAGNOSTIC INFORMATION
Looks like I can't use SUBSTR in UPDATE commands... Is it possible?? |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
Substr can only be a source not a destination, in order to do what you want you have to combine substrs a literal and concatenation.
This is untested but should work or get you close. Just add your where clause. -- This will fail if ABUR_RS is less than 116 characters long.
| Code: |
SET ABRU_RC = SUBSTR(ABUR_RC, 1, 114) CONCAT '5' CONCAT SUBSTR(ABUR_RC, 116, LENGTH(ABUR_RC - 115))
|
|
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Quote: |
| Looks like I can't use SUBSTR in UPDATE commands... Is it possible?? |
Yes it is. Modify your sql like the one below. I have assumed the col-definition of ABUR_RC_TX as CHAR(120). You should modify the length-parameter in the SET statement - SUBSTR(ABUR_RC_TX,116,5) as per the actual definition.
| Code: |
UPDATE TABLE.TABLE
SET ABUR_RC_TX = SUBSTR(ABUR_RC_TX,1,114) || '5' || SUBSTR(ABUR_RC_TX,116,5)
WHERE SVC_CD = '00'
AND MPS_ERR_ID = '230'
AND MSG_RC_ID = '010250'
AND SUBSTR(ABUR_RC_TX,115,1) = '2'; |
|
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Craq,
I m sorry, kept this thread open for a long time and could n't see your post.  |
|
| Back to top |
|
|
|
|
