RE: smallint-->char?
----------
From: kistler@an.hp.com[SMTP:kistler@an.hp.com]
Sent: Wednesday, March 04, 1998 7:40 AM
To: informix-list@iiug.org
Subject: smallint-->char?
I've changed the datatype on a column from smallint to character and
then am trying to "update" the entries to map the "old" integers to
letters:
0 --> n (none)
1 --> l (low)
2 --> h (high)
and I get error 1213 "A character to numeric conversion process failed"
when I run:
update table_A
set col_1 = "n"
where col_1 = "*0*"
JL: Is this an '=' or a MATCHES? Assuming the latter...
Have you checked that the values in the column are all either 0 or 1 or 2?
If so, you shouldn't need to use MATCHES and '=' should be OK.
I would be tempted to create a mapping table:
CREATE TEMP TABLE Mapping (old CHAR(1), new CHAR(1));
INSERT INTO Mapping VALUES ('0', 'n');
INSERT INTO Mapping VALUES('1', 'l');
INSERT INTO Mapping VALUES('2', 'h'); -- Add other values if appropriate!
UPDATE Table_A
SET Col_1 = (SELECT New FROM Mapping WHERE Old = Table_A.Col_1)
WHERE Col_1 IN (SELECT Old FROM Mapping);
SELECT * FROM Table_A WHERE Col_1 NOT IN (SELECT New FROM Mapping);
The UPDATE fixes all the valid values; the SELECT reports on the invalid values.
Yours,
Jonathan Leffler (jleffler@visa.com) #include <bother.ms-exchange.h>
...etc for "low" and "high"
The "update" substitution seems to happen okay, but the error message
interrups the process after each update, so I'm not able to automatically
update a bunch of rows!
Any help would be greatly appreciated.
Thanks,
Jim
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=#=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Jim Kistler - kistler@an.hp.com # Some see issues and ask, "Why?
Hewlett-Packard Co. Imaging Systems # Others see opportunities and
3000 Minuteman Road, Andover, MA 01810 # ask, "Why not?"
TEL: 978-659-2251 - FAX: 978-687-7265 #
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=#=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-