I like this - but as one who has done a lot of screens, I prefer to stem the tide before it gets over the wall. In other words, put an edit in the program that puts up the maintenance screen - this might reduce time spent in the database. And it seems easier to me to do that than to check for a constraint error and then feed that back to the screen. Is that actually simple to do?


-------------- Original message ----------------------
From: CRPence <crp@xxxxxxxxxxxxxxxxxxxx>
The one-time verification was performed... But what has been done to
prevent a recurrence of improper data in that field? Consider adding a
check constraint. The following does not match the given one-time
verification, but I inferred that the comment about spaces being allowed
was only for trailing blanks:

alter table TheLib.TheFile
add constraint TheLib.ck_cnmacc
check ( TRANSLATE(rtrim(cnmacc)
= left('AAAAAAAAAA', length(rtrim(cnmacc)))
) /* S & A must be part of valid non-blank */

The above prevents embedded blanks but not all-blanks. Probably
all-blanks is intended to be diagnosed as invalid too? If so, then
adding "AND CNMACC<>''" to the logic of the CHECK would prevent the case
of all-blanks [the empty string] as well.

If the above logic effects the desired, then the SELECT verification
would need to be repeated and those rows [failing the validation] must
be corrected, before adding the constraint.

Regards, Chuck
All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my

Francis Lapeyre wrote:
We've already verified that there are no lowercase letters anywhere
in the field in any of the databases. Without the UPPER clause, I
get the same results. Thanks, though - good point.

John Arnold (MFS) wrote:


SELECT cnmacc
FROM arstageeo/arcnm
<> ' '

Should work if you also want to see lower case letter values.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by 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 on our policy page. If you have questions about this, please contact [javascript protected email address].