Re: Posting from the Informix-list

Sadagopan, Sathish, CFCTR wrote: > > 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 ? Er, not tested and very much off the seat of pants... CHECK (DATE(char_col) > DATE('01-01-1900') The idea is to force a date conversion. Check constraints work with violations tables but I'm not sure that check constraints with errors do. Try it and see! > 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/ > > ---