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.
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:
WHERE TRANSLATE(cnmacc,' ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ')
<> ' '
Should work if you also want to see lower case letter values.