RE: Need help with SQL SET statement

You cannot change a part of a field by itself using the substr function -

update mylib/myfile set myfld = substr(myfld, 1, 5) concat '0000' concat substr(myfld, 10) where substr(myfld, 6, 8) = ' '

You only need 1 blank in the test for blanks starting at 6 for 8 characters.

Since I don't know how long myfld is, I added the concatenation starting with position 10, to get the rest of myfld into the expression.

HTH
Vern

-------------- Original message --------------
From: "Shaheen Ahmed" <Syed.Ahmed@xxxxxxxxxxxxxxx>

Roger,

UPDATE MYLIB/MFFILE SET substr(myFLD,6,4) ='0000'
where substr(myFLD,6,8) =' '

This is not working. Got error message "Token ( was not valid. Valid tokens: =.
"

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Reger, Bill
Sent: Thursday, June 26, 2008 12:10 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Need help with SQL SET statement


UPDATE MYLIB/MFFILE SET substr(myFLD,6,4) ='0000'
where substr(myFLD,6,8) =' '

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.