Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle 10g - Putting a where clause on a column with a check constraint.
Jon wrote:
> 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.
I see no reason not to open a TAR and report this one if a search of metalink doesn't show that someone beat you to it.
SQL> alter table jon5 add (CCC varchar2(1));
Table altered.
SQL> update jon5 set ccc = aaa;
4 rows updated.
SQL> commit;
Commit complete.
SQL> select * from jon5 where ccc = 'Y';
A BBB C
- ---------- -
Y Happy Y Y Sad Y Y Angry Y
SQL> select * from jon5 where aaa = 'Y';
no rows selected
SQL>
-- Daniel A. Morgan http://www.psoug.org damorgan_at_x.washington.edu (replace x with u to respond)Received on Wed Mar 22 2006 - 13:58:16 CST