Re: create table error with constraint...

On Jul 25, 11:13 am, t..._at_panix.com (Dan Blum) wrote:
> Volker Hetzer <firstname.lastn..._at_ieee.org> wrote:
> > Hi!
> > I've got a simple problem with a create table statement:
> > CREATE TABLE HB_ATTRIBUTE (
> >         STRINGVALUE VARCHAR2(4000 CHAR),
> >         DATEVALUE DATE,
> >         CONSTRAINT TCC_HB_ATTRIBUTE_1 CHECK ((STRINGVALUE is null)<>(DATEVALUE is
> > null)) DEFERRABLE INITIALLY DEFERRED);
> > This gives me an "ORA-00907: missing right paranthesis" at the "<>".
> > I have no idea what I'm doing wrong.
> > I'm using 10.2.0.1.0 on linux 32 bit.
> > Can anybody help me out?
>
> You can't compare boolean values that way in SQL. You can do something like this:
>
> ((decode(stringvalue, NULL, 1, 0) != decode(datevalue, NULL, 1, 0))
>
> --
> _______________________________________________________________________
> Dan Blum                                                 t..._at_panix.com  
> "I wouldn't have believed it myself if I hadn't just made it up."

Dan is right. Under the ANSI/ISO standards NULLs are never equal to anything.

You could also use the "is null" clause to define your check constraint if think this format is easier to read.

UT1 > get t8
  1 alter table marktest
  2* add constraint marktest_ck check (fld1 is not null OR fld2 is not null)
UT1 > /

Table altered.

HTH -- Mark D Powell -- Received on Fri Jul 25 2008 - 14:17:41 CDT