Is this ANSI ? (Was: Querying a number column.)

From: Matthias Rogel <rogelREMOVE_at_THISweb.de>
Date: Fri, 24 Jan 2003 13:49:28 +0100
Message-ID: <b0rcoo$snskn$2@ID-86071.news.dfncis.de>

(to continue Robert's example)

SQL> select *

   2 from t
   3 where mynumber='a';
where mynumber='a'

                *

FEHLER in Zeile 3:
ORA-01722: Ungültige Zahl

As I understand from the discussion so far, in Oracle7 and lower this didn't throw an exception (cannot verify here), whereas in >=8 throws an exception.

I doubt this behaviour is ANSI-SQL

matthias

robertsboyle wrote:
> Mattias is correct..running under 9.2 i get the following..
>
> Elapsed: 00:00:00.02
> robert_at_DEV1>create table t (mynumber number);
>
> Table created.
>
> Elapsed: 00:00:00.00
> robert_at_DEV1>create index x_t_mynumber on t(mynumber);
>
> Index created.
>
> Elapsed: 00:00:00.00
> robert_at_DEV1>insert into t values (1);
>
> 1 row created.
>
> Elapsed: 00:00:00.00
> robert_at_DEV1>insert into t values (2);
>
> 1 row created.
>
> Elapsed: 00:00:00.00
> robert_at_DEV1>set autotrace on explain
> robert_at_DEV1>select *
> 2 from t
> 3 where mynumber = '1';
>
> MYNUMBER
> ----------
> 1
>
> Elapsed: 00:00:00.00
>
> Execution Plan
> ----------------------------------------------------------
> SELECT STATEMENT Optimizer=CHOOSE
> 1 0 INDEX (RANGE SCAN) OF 'X_T_MYNUMBER' (NON-UNIQUE)
>
>
>
> robert_at_DEV1>select *
> 2 from t
> 3 where mynumber = 1;
>
> MYNUMBER
> ----------
> 1
>
> Elapsed: 00:00:00.00
>
> Execution Plan
> ----------------------------------------------------------
> SELECT STATEMENT Optimizer=CHOOSE
> 1 0 INDEX (RANGE SCAN) OF 'X_T_MYNUMBER' (NON-UNIQUE)
>
>
>
> robert_at_DEV1>select *
> 2 from t
> 3 where mynumber = to_number('1');
>
> MYNUMBER
> ----------
> 1
>
> Elapsed: 00:00:00.00
>
> Execution Plan
> ----------------------------------------------------------
> SELECT STATEMENT Optimizer=CHOOSE
> 1 0 INDEX (RANGE SCAN) OF 'X_T_MYNUMBER' (NON-UNIQUE)
>
> Robert.
>
> --
> Posted via http://dbforums.com
Received on Fri Jan 24 2003 - 06:49:28 CST