create table error with constraint...

#1

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?

Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.

#2

Volker Hetzer <firstname.lastname (AT) ieee (DOT) org> wrote:

Quote:

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 tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

#3

Volker Hetzer <firstname.lastname (AT) ieee (DOT) org> wrote:

Quote:

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 tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

#4

Volker Hetzer <firstname.lastname (AT) ieee (DOT) org> wrote:

Quote:

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 tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

#5

Volker Hetzer <firstname.lastname (AT) ieee (DOT) org> wrote:

Quote:

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 tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

#6

On Jul 25, 11:13*am, t... (AT) panix (DOT) com (Dan Blum) wrote:

Quote:

Volker Hetzer <firstname.lastn... (AT) ieee (DOT) 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 (DOT) 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 --

#7

On Jul 25, 11:13*am, t... (AT) panix (DOT) com (Dan Blum) wrote:

Quote:

Volker Hetzer <firstname.lastn... (AT) ieee (DOT) 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 (DOT) 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 --

#8

On Jul 25, 11:13*am, t... (AT) panix (DOT) com (Dan Blum) wrote:

Quote:

Volker Hetzer <firstname.lastn... (AT) ieee (DOT) 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 (DOT) 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 --

#9

On Jul 25, 11:13*am, t... (AT) panix (DOT) com (Dan Blum) wrote:

Quote:

Volker Hetzer <firstname.lastn... (AT) ieee (DOT) 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 (DOT) 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 --

#10

Mark D Powell <Mark.Powell (AT) eds (DOT) com> wrote:

Quote:

On Jul 25, 11:13?am, t... (AT) panix (DOT) com (Dan Blum) wrote:
Volker Hetzer <firstname.lastn... (AT) ieee (DOT) 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 (DOT) com ?
"I wouldn't have believed it myself if I hadn't just made it up."

Quote:

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 (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."