Oracle 10g - Putting a where clause on a column with a check constraint.

From: Jon <jonathan.p.crawford_at_gmail.com>
Date: 22 Mar 2006 11:10:45 -0800
Message-ID: <1143054645.634518.257020@i40g2000cwc.googlegroups.com>

So I have a fairly large problem with Oracle 10g. We have a large number tables that are NVARCHAR (of different lengths) with check constraints on their values.

If I do a select with a where clause on one of these columns. I get "no rows selected" everytime.

Has anyone experienced this? Below is an example.

If I don't have the check constraint on the column it works fine. Also this worked fine in 9i and we are trying to migrate our application onto 10g as new clients are requesting compatability with 10g.

Thanks for all the help.

---

create table jon5
(
AAA NVARCHAR2(1),
BBB NVARCHAR2(10),
CONSTRAINT CC_AAA CHECK(AAA IN('N','Y')), CONSTRAINT CC_BBB CHECK(BBB IN('Happy','Sad','Angry')) );

insert into jon5 values('Y','Happy');
insert into jon5 values('Y','Sad');
insert into jon5 values('Y','Angry');
insert into jon5 values('N','Sad');

select * from jon5
A BBB
- ----------

Y Happy
Y Sad
Y Angry
N Sad

4 rows selected.

select * from jon5 where AAA='Y'
no rows selected

select * from jon5 where AAA>'N'
A BBB
- ----------

Y Happy
Y Sad
Y Angry
3 rows selected.

select * from jon5 where AAA like '%'
no rows selected

select * from jon5 where BBB like '%'
no rows selected

select * from jon5 where BBB > 'Angry'
A BBB
- ----------

Y Happy
Y Sad
N Sad

3 rows selected.

select * from jon5 where BBB = 'Angry'
no rows selected

select * from jon5 where BBB != 'Angry'
A BBB
- ----------

Y Happy
Y Sad
N Sad

3 rows selected. Received on Wed Mar 22 2006 - 13:10:45 CST