Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2010
    Posts
    5

    Unanswered: Need help with set of substring

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 || '%'

  4. #4
    Join Date
    Jul 2010
    Posts
    5

    Thumbs up This looks good.

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •