Re: error after altering tables

On Mon, 3 Aug 1998, Sahrul Hidayat wrote: > I'm facing a problem with my databasa, INFORMIX 7.24 on HP-UX 10.01. > Heading Y2K, we are going to convert our data, some of tables we alter to > expanded the char space. We have some YEAR INDICATOR which this field is > using CHARACTER type. Before the altering this field only have 2 (two) > char, now we alter to be 4 (four). This field is only indicator, eq. > "95", "96" and so on. But now we got a problem, when we are trying to > select these tables, which have this kind field, we cannot select this > field, we have tried also using space, failed. The error number is 750, > mentioned we have to try UPDATE STATISTICS, We tried UPD STAT, and then > select again, STILL FAILED...??? > Does anybody have this experienced or suggestion...????? First off - convert your DATE fields to DATE (or DATETIME YEAR TO DAY) type. Anything else is frankly ludicrous, and CHAR is especially ludicrous. For your year indicator fields, use SMALLINT (2 bytes; handles years out to 32768 AD, which should be longer than your software lasts). With that out of the way, we have to interpret your question. You had a table something like: CREATE TABLE SomeTable ( ... YearIndicator CHAR(2) NOT NULL, ... ) and you did something like: ALTER TABLE SomeTable MODIFY (YearIndicator CHAR(4) NOT NULL); Presumably, you now need to do an UPDATE to convert your 2-digit years into 4-digit values. Given that you're using 7.24, you can probably do it with a statement similar to: UPDATE SomeTable SET YearIndicator = "19" || TRIM(YearIndicator); However, before getting that far, you seem to have run into a problem which, if finderr is to be believed, should be reported to Informix: -750 Invalid distribution format found for table_name. This internal error should not occur unless the database has been corrupted in some way. To rebuild the distribution, use UPDATE STATISTICS. If the error recurs, please note all circumstances, and contact the Informix Technical Support Department. Since you can read as well as I can, what is stopping you from contacting Informix Technical Support. Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.59 -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn