Need help with set of substring

Deb Locy · #1 (**permalink**) 07-09-10, 16:52

I am trying to execute the following but am getting a syntax error. DB2 version I am using is 8.1.

UPDATE SCE7000
SET SUBSTR(VNDRNOTE1RSLT001,19,9) = '777777777'
WHERE SUBSTR(VNDRNOTE1KEY,1,4) = 'SAGE'
AND SUBSTR(VNDRNOTE1KEY,5,10) = :WS-SEL-VNDR-NBR
AND SUBSTR(VNDRNOTE1RSLT001,19,9) = :WS-SEL-SS-ID

Any suggestions would be greatly appreciated.

ARWinner · #2 (**permalink**) 07-09-10, 17:01

I think you want something like :

Code:

UPDATE SCE7000
SET VNDRNOTE1RSLT001 =  SUBSTR(VNDRNOTE1RSLT001,1,18) || '777777777' ||SUBSTR(VNDRNOTE1RSLT001,28) 
WHERE SUBSTR(VNDRNOTE1KEY,1,4) = 'SAGE'
AND SUBSTR(VNDRNOTE1KEY,5,10) = :WS-SEL-VNDR-NBR
AND SUBSTR(VNDRNOTE1RSLT001,19,9) = :WS-SEL-SS-ID

Andy

tonkuma · #3 (**permalink**) 07-09-10, 17:28

I agree with Andy.

Additional notes.
Note(1):
I doubt table design, because many SUBSTRs are used for columns.
It would be better to separate columns, like:
VNDRNOTE1KEY
---> VNDRNOTE1KEY_1 CHAR(4) NOT NULL , VNDRNOTE1KEY_VNDR_NBR CHAR(10) NOT NULL , .....
VNDRNOTE1RSLT001
---> VNDRNOTE1RSLT001_1 CHAR(18) , VNDRNOTE1RSLT001_SS_ID CHAR(9) , .....

Note(2):
There may be a possibility to get better performance by modifying conditions...

Code:

/*
 WHERE SUBSTR(VNDRNOTE1KEY , 1 ,  4)     = 'SAGE' 
   AND SUBSTR(VNDRNOTE1KEY , 5 , 10)     = :WS-SEL-VNDR-NBR 
*/
 WHERE VNDRNOTE1KEY LIKE 'SAGE' || :WS-SEL-VNDR-NBR || '%'

Deb Locy · #4 (**permalink**) 07-12-10, 07:31

The table design is delivered vendor code so it cannot be modified. However, the changes for the SQL look good and I will be testing them today. Thanks so much for the prompt responses. I am very optimistic about your suggestions.

Internet Services

Web Development

Digital Marketing

Consumer Tech