Query in Pervasive Control Center 1

[Solstorm]

Is there a way to run a query to change all information for a column named EmployID from a mix of lowercase/uppercase to all uppercase in every table in a database that contains this column? Looking to do this all at one time.

Example:

I have about 30 tables that have EmployID as one of the columns and these have a mix of employee ID's that contain alpha/numeric characters. I want the alpha characters to be all uppercase.

AB123XxxxxxXX to be AB123XXXXXXXX.

If it can be done but only one table at a time what can I run to find out which tables have EmployID in them.

thanks in advance!!

 

[TomKane]

Hi,

To set the field to be upper case you can use the ucase or upper function as below:

update employid set employid = upper(employid)

To find out which tables have emplyid in them you can use the X$Field table to select all fields named employid. The X$Field table also contains a xe$file field which contains the file id which can be used in the X$File table to look up the names of the files.

Hope this helps.
Tom

 

[Solstorm]

Tom,

Thanks for the reply. I found it very helpful but some of the tables I tried to change had this as a non modifiable key. Well it was worth a shot.

Storm

 

[TomKane]

Maybe the thing to do there is to use BUTIl -SAVE to dump the file, create a new file, change the dumped data and then load them back into the new file.