Re: Constraint comparing blanks.

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 appropriate.

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]:

<code>

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*/

</code>

Regards, Chuck