Alan Campin wrote:
When you are declare DDL for an SQL defined table we use a lot of
stuff like this: NAME <> ' ';
I would have thought that this would expand to be compare say 10
char name to 10 char blank but I saw today that is not true.
If you have the following in a field name the test will fail
because it only looks at the first character.
<b>THOMAS<b><b><b> where <b> is a blank.
What is the correct way to declare this test? In RPG you have a
*ALL' ' but not in SQL.
You surely don't want to code: NAME <> ' '
I know there has to be a simple way to do it. Just can't think of
it off the top of my head.
Since the described as a generally expected outcome reflects what
should typically occur, a failing case would appear to be a defect.
Perhaps a more explicitly detailed failing scenario would be more
Since the subject of the message notes both a constraint and
comparing, and because the shown predicate is an apparent column
name compared to a literal, and since DDL is noted, then presumably
the concern is with how a SQL CHECK CONSTRAINT is operating. Thus
the DDL for the SQL CREATE TABLE and\or the SQL ALTER used to ADD
CONSTRAINT of the CHECK constraint would probably be ideal to
introduce the scenario setup. Additionally the environment in which
the statement(s) was issued may be relevant; e.g. at least each of:
job and source CCSID, if issued from an ASCII client, within\from
which utility, sort sequence setting. Finally the statement that is
issued would be of interest; i.e. the INSERT or UPDATE, for which a
constraint violation is apparently desired, but not transpiring.
To confirm the existence and expected function of the constraint
that was defined, both the DSPFD TYPE(*CST) for the TABLE along with
the output from the following SQL are worthwhile: SELECT
CONSTRAINT_STATE, ENABLED, CHECK_PENDING, CHECK_CLAUSE FROM
QSYS2/SYSCST T INNER JOIN QSYS2/SYSCHKCST C ON
TABLE_NAME='the_table_name' AND T.CONSTRAINT_NAME = C.CONSTRAINT_NAME
FWiW, when I try the following on V5R3 in a CCSID(37) job and
with the SRTSEQ(*HEX) STRSQL session attribute established, the
constraint properly disallows a name of all blanks, but allows the
name prefixed with a blank [as described in the quoted text]:
create table chkname
(name char(10) /* works also with: CCSID 1208 */
,check (name <> ' '))
insert into chkname values(' THOMAS ')
; /* constraint allows the value for insert */
insert into chkname values(' ')
; /* fails w/ SQL0545 CHECK CONSTRAINT violation */
update chkname set name=' JOHN' where name <> ' '
; /* constraint allows similar value for update */
insert into chkname values(NULL)
; /* a NULL values does not violate the constraint*/