ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Update substring

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Update substring

    Dear all

    I want to update substring on a colum like under but error about token? I do not how to shooting.

    UPDATE SYSLIB1/CUSPF
    SET SUBSTRING(rp01,35,21)=' '
    where substring(rp01,1,2) ='PG'

    Eror: Token ( was not valid. Valid tokens: =. ???why?

    Cuspf: file
    syslib1: liblary
    rp01: field

  • #2
    Re: Update substring

    Do you have a space between the quotes for ;
    SET SUBSTRING(rp01,35,21)=' '
    ?



    On another note, it is a horrible practice to hard code the libaries in SQL (or in anything else for that matter).
    Hunting down the future ms. Ex DeadManWalks. *certain restrictions apply

    Comment


    • #3
      Re: Update substring

      Try this if length of rp01 is 55.

      Code:
      UPDATE CUSPF
      SET rp01 = substr(rp01, 1, 34) !! substr(' ', 35, length(rp01 - 34))
      where substring(rp01, 1, 2) ='PG'
      Philippe

      Comment


      • #4
        Re: Update substring

        Originally posted by Mercury View Post
        Try this if length of rp01 is 55.

        Code:
        UPDATE CUSPF
        SET rp01 = substr(rp01, 1, 34) !! substr(' ', 35, length(rp01 - 34))
        where substring(rp01, 1, 2) ='PG'
        Thank for all reply

        HI, Mercury

        In this case we do not appoint library for this file?

        Mercury, I remember that last time you said : you will teach me (the thread i posted before)......by french? why you do not show to me how to do by English? are you joking?
        I need to be export data to excel neatly based on spoolfile but on direct file .
        Can you help me.
        Thank you.
        Tintin

        Comment


        • #5
          Re: Update substring

          Originally posted by tintin
          Mercury, I remember that last time you said : you will teach me (the thread i posted before)......by french? why you do not show to me how to do by English? are you joking?
          I need to be export data to excel neatly based on spoolfile but on direct file .
          Not sure to understand you, could you clarify ?

          BTW what is your native language ?
          Philippe

          Comment


          • #6
            Re: Update substring

            Yes, give an example for my thread
            Eg: Spoolfile 1

            Name Amount
            Account
            ---------------------------------
            Ms A 500
            0000007
            Ms B 900
            0000006

            Spoolfile 2

            Name Account Amount
            ---------------------------------
            Ms A 0000007 500

            Ms B 0000006 900

            I want create a new spoolfile 2 based on a spoolfile1
            How to do? can you show to me?
            thank you.
            Last edited by tintin; October 11, 2007, 09:46 AM.

            Comment


            • #7
              Re: Update substring

              This is a strange request. It's quite impossible to create a new spooled file from another spooled file. You'd better off creating a new edit program to create the new spooeld file in the way that you want it to be. BTW what's the link with the update stm in your first post ?
              Philippe

              Comment


              • #8
                Re: Update substring

                Originally posted by Mercury View Post
                This is a strange request. It's quite impossible to create a new spooled file from another spooled file. You'd better off creating a new edit program to create the new spooeld file in the way that you want it to be. BTW what's the link with the update stm in your first post ?

                Comment


                • #9
                  Re: Update substring

                  Well follow the instructions given there. Jamie and Jonas gave you a good way/thread to carry out.
                  Philippe

                  Comment

                  Working...
                  X