Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Update positioning varchar columns

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

New User


Joined: 03 Aug 2005
Posts: 17

PostPosted: Wed Nov 12, 2008 7:33 am    Post subject: Update positioning varchar columns
Reply with quote

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

dick scherrer

Site Director


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

PostPosted: Wed Nov 12, 2008 7:43 am    Post subject:
Reply with quote

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
View user's profile Send private message
dirceu.alves

New User


Joined: 03 Aug 2005
Posts: 17

PostPosted: Wed Nov 12, 2008 7:53 am    Post subject:
Reply with quote

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
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Nov 12, 2008 8:49 am    Post subject:
Reply with quote

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
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Wed Nov 12, 2008 9:25 am    Post subject:
Reply with quote

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
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Wed Nov 12, 2008 9:29 am    Post subject:
Reply with quote

Craq,

I m sorry, kept this thread open for a long time and could n't see your post. icon_sad.gif
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 Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 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
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us