Re: create table error with constraint...

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Sat, 26 Jul 2008 06:18:16 -0700 (PDT)
Message-ID: <48798a52-456b-4886-ad84-c43a2a95b4f7@z26g2000pre.googlegroups.com>


On Jul 25, 3:29 pm, t..._at_panix.com (Dan Blum) wrote:
> Mark D Powell <Mark.Pow..._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                                                 t..._at_panix.com
> "I wouldn't have believed it myself if I hadn't just made it up."- Hide quoted text -
>
> - Show quoted text -

Apparently my mistake as I read the OP as not wanting both columns to be null at the same time.

  • Mark D Powell --
Received on Sat Jul 26 2008 - 08:18:16 CDT

Original text of this message