RE: Posting from the Informix-list
A table which has violations started on it cannot be modified/altered , (err no 898). Peter , Could you explain how the check constraint would help here ? Alternately , you could create a dummy table , create an insert trigger calling a procedure which does the validation to decide if the row is dirty and then update the original table if the row is dirty. Sathish.S > ---------- > From: Peter Lancashire[SMTP:Peter.Lancashire.PL1@bayer.co.uk] > Sent: Thursday, October 22, 1998 12:15 PM > To: informix-list@iiug.org > Subject: Re: Posting from the Informix-list > > Blacker, Herb wrote: > > > > I have an ASCII pipe-delimited file from another database which contains > > dates that have had no error checking done on them (don't even ask!). > > The result is that there are invalid dates in the date field. Have any > > of y'all done any after-the-fact file manipulation to determine invalid > > dates? What we'd like to do is substitute a single valid default date > > for all invalids. I'm looking for a 'C', awk or sed program to do this. > > > > Thanks in advance, > > Herb Blacker > > Database Administrator > > hhblack@prninc.com > > You might like to look at the Informix SET VIOLATIONS facility in > version 7.1+. You could use it like this: > > Load your dubious dates into a char column. > > Set violations etc > > Alter the column to type date > > Look in the violations tables, which will identify the problem rows. > > Write an update statement to set the dates, using the row identification > in the violations table. > > I haven't tested this - I'm not sure that the violations facility works > when changing the column type. If not, try creating a check constraint > that does a date conversion - check constraints do work. > > Good luck. > -- > Peter Lancashire > Information Systems Specialist, Bayer plc > Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK > Tel: +44-1635-562258, Fax: +44-1635-562281 > --- > If all else fails, read the instructions and the release notes. > Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/ > --- >