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)