Re: Dazed and confused

It all depends what values are stored in XCODE in the table. When you use XFLAG=0, some of the XCODE values are non numeric. Thus the failure.
(Do you have an index on XFLAG?)
When you use XFLAG=1 the result set is all numeric.

However, the proper way to do the select is to always place XCODE values in quotes since
it is a char.

SQL> SELECT COUNT(*) FROM MYTABLE WHERE XCODE='1234567890123' AND XFLAG=1;  Stephen C. Ashmore
Brainbench MVP for Oracle Administration http://www.brainbench.com
Author of: 'So You Want to be an Oracle DBA?'

"mar" <mar_at_notexist.com> wrote in message news:bmdqg0$l3ne0$1_at_ID-174077.news.uni-berlin.de...
>
> 8i Ent
>
>
> DESC "MYTABLE":
> --------------------------
> XCODE, NOT NULL, CHAR(15)
> XFLAG, NOT NULL, NUMBER(1)
> --------------------------
>
>
> SQL> SELECT COUNT(*) FROM MYTABLE WHERE XCODE=1234567890123 AND XFLAG=1;
>
> COUNT(*)
> ----------
> 4
>
>
>
> SQL> SELECT COUNT(*) FROM MYTABLE WHERE XCODE=1234567890123 AND XFLAG=0;
>
> SELECT COUNT(*) FROM MYTABLE WHERE XCODE=1234567890123 AND XFLAG=0;
> *
>
> ERROR at line 1:
> ORA-01722 invalid number
>
> (Orcl says XCODE receives an invalid number)
>
>
>
> Questions:
>
> 1) why I am allowed to use XCODE=1234567890123 in the WHERE clase at all,
> since XCODE is a CHAR(15)
>
> 2) why do I get the error above when using XFLAG=0, but not with XFLAG=1
>
> Thanks
>
>
>
>
Received on Mon Oct 13 2003 - 05:28:03 CDT