I mistakenly set up a table with a column as varchar although it would only contain decimal numbers. I thought I could get away with not changing the datatype, it’s a split database and at the time I thought I couldn’t be bothered with any sql. But now I need to multiply that column’s values by a fixed number which is not possible probably because of the data type. When I try to change the data type with this:
ALTER TABLE “Table1” ALTER COLUMN “Column2” DECIMAL (3,2)
I get the following error message:
data exception: invalid character value for cast in statement [alter table “Table1” alter column “Column2” decimal (3,2)]
Is there something wrong with my syntax or is the error due to the fact that I am trying to convert an already populated column? Is there an easy way of converting the column without losing any data?
There are some problems with a split DB I’ll try to explain.
First, running a test with your setup only gave me a problem if using such a small length for the DECIMAL field. Increasing it to 10,2 worked without problem.
After the ALTER statement, if you edit the table, it appears that the field hasn’t changed. It actually has and if you run an SQL statement with a calculation on the field, you will see it works. To see the actual modified version of the table, run the following SQL command (of course with your table name):
SELECT * FROM "INFORMATION_SCHEMA"."COLUMNS" WHERE "TABLE_NAME" = 'Your table name here' ORDER BY "ORDINAL_POSITION"
You will then see the field has actually changed.
Changing field length did not seem to have any effect. I tried a calculation to check like you suggested but got the same error message. Where do I run that sql command you quoted? Running it in the main BASE window’s Tools>SQL… gave me a result of “]”.
I think you might have entered alter table “Table1” alter column “Column2” decimal (3,2)] instead of alter table “Table1” alter column “Column2” decimal (3,2)
@a_lega your comment is unclear. Did the ALTER statement with a larger field work (command completed successfully) or did it fail? Based on the comment, you tried a calculation after the change, so it seems you are saying it worked. If it did fail, you probably have non-numeric characters somewhere in that column which must be removed in order to run the ALTER command successfully. The SELECT statement is easiest to see results in QUERY.
I am sorry for the unclear comment. I tried the following SQL command:
ALTER TABLE “Table1” ALTER COLUMN “Column2” (10,2)
The command failed. I tried the calculation in case the alteration had registered anyway, despite the error message (I know that’s not what you meant), which ofcourse also failed.
I suppose what I have to do is run a query with a criterion that would show me records with hidden gaps or non num chars?
it took a bit and I’m certainly no expert with SQL but this worked for me to find records that were other than numeric:
SELECT ID, T1."Your-Field-Name" FROM "your-Table-Name" T1
LEFT JOIN (select "Your-Field-Name" from "Your-Table-Name" where (CASE WHEN REGEXP_MATCHES(TRIM("Your-Field-Name"), '[0-9]+') THEN CAST (TRIM("Your-Field-Name") AS INTEGER) ELSE NULL END) > '0') T2 ON T2."Your-Field-Name" = T1."Your-Field-Name" WHERE T2."Your-Field-Name" IS NULLRun as Query - ‘Run SQL command directly’ turned on. Result is records with spaces or nun-numeric characters which must be corrected.
Unfortunately, if your database is embedded, the SELECT statement will NOT work. You must be using a newer HSQL. If this is the case I have no further suggestions at this time.
I ran that sql and got this error message:
Κατάσταση SQL: 42501
Κωδικός σφάλματος: -5501
user lacks privilege or object not found: ID
My database is split and I am using Libreoffice 5.2.1.2 (x64), by the way.
Change ID to a field name in your table where you can uniquely identify records.
To extend Ratslinger’s helpful “Change ID…” suggestion, I like to name my tables in the plural, (since they contain multiple records) e.g. Items, States, and Categories. Then I like to give my primary key the table’s name in singular, e.g. Item ID, State ID, and Category ID. Furthermore, any name field in the table also get a singular name, e.g. Item, State, and Category. By naming things this way I found it eased keeping track of how things worked, especially when it comes to joining tables.
Cont… 1) You can write this as: “Item_ID” or ItemID if you like to avoid having to quote it, i.e. [Item ID].* 2) Also name your foreign key the same, i.e. Item ID, State ID and Category ID. (*Regarding the use of spaces in identifiers, I got spoiled in Access by the fact that the query editor would auto-quote for me, so got used to the space being easy to use and improving readable.)
Guessing you have some text which can’t be automatically converted. Hard to say what it is. Could be a leading space or something.
First, suggest you sort out if your alter command works at all. So create a simple test database and try your alter command on it as follows:
- while it’s empty,
- with just one record having an integer,
- then with a decimal like 4.23,
- then with the string " ", etc.
Perhaps it will come to you what’s going on.
Failing that, usually when I get stuck like you are, I just create a new field, and figure out how to copy my values to it, stumbling over what I must to move the data, either manually, via a SQL update query or sometimes using a Basic program to manually convert and copy the data from old field to new field, then abandon and delete the original field. When I’m done deleting the old field I rename the new field to the old field’s name. Yes, I know this is a bit of trouble, but it is what has worked for me on simple and harder fixes.
I am still learning how to do some of this myself on Base, but these are the methods I have used for many years on Access and many other data bases before that.