SQL0401N on Check constraint on UDT column.

[sql-db2-dba]

Why should this be incompatible data types when CD is really VARCHAR??

D:\DBA\LDS>db2 "CREATE DISTINCT TYPE CD AS VARCHAR( 5 ) WITH
COMPARISONS"
DB20000I The SQL command completed successfully.

D:\DBA\LDS>db2 "CREATE TABLE X1 (COL1 CD NOT NULL DEFAULT 'I' CHECK
(COL1 IN ('A', 'I')) )"
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0401N The data types of the operands for the operation "IN" are
not
compatible. SQLSTATE=42818

[Serge Rielau]

That's what makes distinct types distict.
You need to cast col1 to the base type (or the inlist arguments to CD).
Essentially SQL makes you assure that you really want to comaper 'T' with CD
rather than just did so by accident.

Cheers
Serge

[Knut Stolze]

Serge Rielau <sr*****@ca.eyebeem.com> wrote:

That's what makes distinct types distict.
You need to cast col1 to the base type (or the inlist arguments to CD).

Or the other way around:

CREATE TABLE x1(col1 cd NOT NULL DEFAILT 'I' CHECK (col1 IN (cd('A'),
cd('I'))) )

--
Knut Stolze
Information Integration
IBM Germany / University of Jena