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?
Please reference the post below:
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.
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
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.
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%'
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
Katherine, IF you don't have many records in issue table(for example, not a million records), you could
pdm_extract -f "select id, zCustomer_Zipcode from Issue" > mydata.txt
--go ahead change zCustomer_Zipcode data type in SDM and database
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 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.