MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » May 2014

Re: can this reject bad data?



fixed

There are two steps.

Step 1: Finding existing bad data.
Step 2: Preventing further bad data.

Step 1: Finding existing bad data.
Pretend your FAKEDATE field is 8 characters. And it should be stored as
MMDDYYYY.
And there is no 32 day of December.
INSERT INTO ROB/FUNDOT (FAKEDATE) VALUES('12312014')

Then read about idate at
http://www.think400.dk/downloads.htm
and try
select fakedate,
iDATE(FAKEDATE,'*MDCCYY')
from rob/fundot
order by 2 desc

....+....1....+...
FAKEDATE IDATE
12322014 -
12312014 12/31/14
******** End of data

Step 2: Preventing further bad data.
Ideally, if you switched to 'real' date fields you would no longer have
bad dates.
However, if you decide that the cost of switching to real dates is very
high and are interested in an alternative then you may wish to see if the
prevention can be done via a 'check' constraint. Pretend your FAKEDATE
field is 8 characters. And it should be stored as MMDDYYYY. Then try
this:
ADDPFCST FILE(ROB/FUNDOT) TYPE(*CHKCST) CHKCST('date(substr(fakedate,5,4)
concat ''-'' concat substr(fakedate,1,2) concat ''-'' concat
substr(fakedate,3,2)) between date(''2000-01-01'') and
date(''2099-12-31'')')

A good date still goes in fine:
INSERT INTO ROB/FUNDOT (FAKEDATE) VALUES('12312014')
1 rows inserted in FUNDOT in ROB.
Then if you try to add a bad date:
INSERT INTO ROB/FUNDOT (FAKEDATE) VALUES('12322014')
INSERT, UPDATE, or MERGE not allowed by CHECK constraint.

Of course, if you add a bad date in a RPG program it will error out also.
Now, if the logic in your RPG program assumes that any row addition error
is only possible because the row already exists and then you must try an
update instead of a write....
Don't laugh, I've seen this crud.

See also:
RMVPFCST FILE(ROB/FUNDOT) CST(*all) TYPE(*CHKCST)

http://www.think400.dk/files/idate_readme.rtf

Rob Berendt





Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact