CA Service Management

Expand all | Collapse all

Integer field - Change to String

Jump to Best Answer
  • 1.  Integer field - Change to String

    Posted 03-05-2019 02:58 PM

    Way back in the day, via Web Screen Painter > DB Schema > ISS table, a custom field (zCustomer_Zipcode) was created as an Integer field type and has worked great until now.

     

    We now have customers from a different state where the zip code begins with a 0 (zero). When we create Issue tickets and enter a zip code that begins with a 0, it is removed when we save the ticket. I believe this is due to it being an Integer and not String.

     

    Is there a way for me to change the field type from Integer to String easily and not corrupt any data I already have entered into the field for my Issue tickets?

     

     

    thank you

    TheKatherine



  • 2.  Re: Integer field - Change to String

    Broadcom Employee
    Posted 03-06-2019 04:19 PM

    Hi Katherine,

     

    Please reference the post below:

     

    https://communities.ca.com/message/241897549?commentID=241897549#comment-241897549 

     

    As noted here please test this out in a NON PROD environment, and take a backup before performing any changes, anytime data already exists there is a chance it can become corrupt.

     

    Thanks,

    Scott



  • 3.  Re: Integer field - Change to String

    Broadcom Employee
    Posted 03-07-2019 04:21 PM

    you could pdm_extract that custom field value + id to a file...change the type from integer to string, both in SDM and mdb...then pdm_load the data back...this way, no matter what happens on dbms side you won't lose a bit of data



  • 4.  Re: Integer field - Change to String

    Posted 03-08-2019 01:00 AM

    Yes. This plus Scott's answer are the full solution.

     

    Well, that and take a full backup before all of this is started, of course.



  • 5.  Re: Integer field - Change to String

    Posted 03-08-2019 09:07 AM

    Chi Chen,

     

    Can you help me out with the pdm_extract and pdm_load steps ... I am not that familiar with those commands (have only used them once before).

     

    Here is the information I have about my custom field if it helps:

     

    my select statement

          select *
          from dbo.wspcol
          where display_name LIKE 'zCustomer%'

     

    my results

          id = 400370
          persid = wspcol:400370
          table_name = iss
          column_name = zCustomer_ZipCode
          status = 3
          schema_name = zCustomer_ZipCode
          display_name = zCustomer_ZipCode
          type = 1             *** will be changed to 2 based upon a step in the other instructions from Scott
          string_len = 0
          is_wsp = 1

     

     

    thank you

    TheKatherine



  • 6.  Re: Integer field - Change to String
    Best Answer

    Broadcom Employee
    Posted 03-08-2019 11:42 AM

    Katherine, IF you don't have many records in issue table(for example, not a million records), you could

    --run

    pdm_extract -f "select id, zCustomer_Zipcode from Issue" > mydata.txt

    --go ahead change zCustomer_Zipcode data type in SDM and database

    --run

    pdm_extract -u -f mydata.txt

    However, if you have too many records, you would better do this from database side and your DBA can assist you with this. Doing pdm_load with a million records will take too long time.

    Thanks _Chi



  • 7.  Re: Integer field - Change to String

    Posted 03-13-2019 09:26 AM

    Chi,

     

    Thanks for all the information, I am going to work with my DBA on creating a new field and then copying the data from the current field to the new field via the backend.

     

     

    thank you

    TheKatherine