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

Matthias,

Supposing "mynumber" is a NUMBER, this query will also fail in 7.3.4.

Peter

"Matthias Rogel" <rogelREMOVE_at_THISweb.de> schreef in bericht news:b0rcoo$snskn$2_at_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 - 07:04:53 CST