Re: Dazed and confused

From: Stephan Bressler <sbresslerx_no_spam_at_arcor.de>
Date: Mon, 13 Oct 2003 14:24:13 +0200
Message-ID: <bme5hc$4uo$1@news.mch.sbs.de>

mar wrote:
> 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

Hi,
oracle accepts comparas of char columns to numeric values, for there is a conversion possible (at least sometimes). I assume you get the error with xflag=1 because all those records contain numeric values for xcode, while at least one of the records with xflag=0 contains a non-numeric value.

Stephan Received on Mon Oct 13 2003 - 07:24:13 CDT