carlosdl Apr 7, 2010   6:17 PM GMT

You could use the CONCAT, CHAR_LENGTH and LEFT functions (if they are available on your platform) or their equivalents. Something like this:

UPDATE mbmtest01/itemaster 
SET ITNBR = CONCAT(LEFT(ITNBR,CHAR_LENGTH(ITNBR)-1),' ') 
WHERE right(ITNBR,1) = 'M'

84,495 pointsBadges:

TomLiotta Apr 8, 2010   1:41 AM GMT

The above UPDATE statement looks reasonable enough. Be aware that using this on fixed-length columns that can have trailing blanks might require the use of %TRIM() to locate the last significant character. Tom

125,585 pointsBadges:

philpl1jb Apr 8, 2010   1:59 AM GMT

Carlos, I'm not certain that the AS/400 SQL has CHAR_LENGTH this will remove trailing 'M' UPDATE mbmtest01/itemaster SET ITNBR = CONCAT(substring(ITNBR,1, LENGTH(trim(ITNBR))-1),’ ‘) WHERE substring(ITNBR, LENGTH(trim(ITNBR)),1) = ‘M’ but will, I fear, fail if the field is blank. Phil

54,090 pointsBadges:

carlosdl Apr 8, 2010   1:52 PM GMT

Yes, it could fail if the field is blank, but the use of negative numbers as parameters for functions like SUBSTRING is allowed in other platforms.

84,495 pointsBadges:

nitzinger Apr 8, 2010   5:19 PM GMT

When I try this code: UPDATE mbmtest01/itemaster SET ITNBR = CONCAT(LEFT(ITNBR,CHAR_LENGTH(ITNBR)-1),’ ‘) WHERE right(ITNBR,1) = ‘M’ I get an: Duplicate key value specified. error How could that be happening Thanks to all

385 pointsBadges:

carlosdl Apr 8, 2010   5:37 PM GMT

Sounds like you have a unique index (or pk) on ITNBR, and when applying this update it would generate duplicates on that column.

84,495 pointsBadges:

nitzinger Apr 8, 2010   5:54 PM GMT

Yes it is a unique index. Is there a way around this?

385 pointsBadges:

TomLiotta Apr 8, 2010   6:34 PM GMT

I’m not certain that the AS/400 SQL has CHAR_LENGTH... The CHARACTER_LENGTH() (and CHAR_LENGTH()) function has been in DB2 for a long time. Not used much, since LENGTH() is shorter. Tom

125,585 pointsBadges:

carlosdl Apr 8, 2010   6:40 PM GMT

Hmmm. There is no way you can insert duplicate values or create them with an update on a column with a unique index. If the column has a unique index on it, it means that no duplicates should exist for that column, so, you should not try to create duplicates. If duplicates are going to be allowed, then you should drop the unique index. You might want to review the results you would get with this update (what rows are going to be affected ? how are they going to be affected ?), and make sure this is the correct way to achive your objectives.

84,495 pointsBadges:

thongtarget21 Apr 9, 2010   2:46 PM GMT

This is great info. Thanks!

485 pointsBadges:

nitzinger Apr 9, 2010   6:30 PM GMT

You're right the key is unique and as I reviewed the data there is a few fields that removing the trailing character 'M' would cause them to become duplicates. No secondary key so this would cause other problems if allowed to proceed. Thanks you are so very right! This IS great stuff.

385 pointsBadges: