Re: create table error with constraint...

Mark D Powell <Mark.Powell_at_eds.com> wrote:
> 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 > /

That's not logically equivalent to the original - he was doing an XOR, essentially.

-- 
_______________________________________________________________________
Dan Blum					         tool_at_panix.com	
"I wouldn't have believed it myself if I hadn't just made it up."