Re: create table error with constraint...
Date: Sat, 26 Jul 2008 06:18:16 -0700 (PDT)
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,
> 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 --