Re: select constant.field >> 10g float and double indicator

"Arto Viitanen" <arto.viitanen_at_csc.fi> wrote in message news:43d87dc3$0$10069$ba624cd0_at_newsread.funet.fi...
> Mark C. Stock wrote:
>
>> but, what indicates to you that one is an integer and one is a floating
>> point? they look identical to me:
>
> SQL*Plus:
>
> SQL> select 1.foo from dual;
>
> OO
> ----------
> 1.0E+000
>
> SQL> select 1."foo" from dual;
>
> foo
> ----------
> 1
>
> On Oracle 10g EE, Linux Redhat 3 (32 bit).
>
> Oracle's Raptor shows 1.foo as 1.0.
>
> But, when I change 1.foo to 1.loo, I get 1 !
>
> I guess it uses some formatter which marks f as float. This can be
> validated with
>
> select 1f from dual
>
> which is legal and it returns 1.0 as result.
>
> --
> Arto Viitanen, CSC Ltd
> Espoo, Finland

look closely at your output and you'll see that 1.foo is parsed as 1.f with a column alias of oo, the equivalent of 1.f as OO

the 'formatter' that you're referring to is the parser -- and it looks like starting with 10g the parser recognizes 'd' and 'f'' as double and float indicators in number literals (see the discussion on Number Literals as well as BINARY_FLOAT and BINARY_DOUBLE data types in the 10g SQL Reference)

    SQL> select 1.foo, 1.doo, 1.goo from dual;

            OO OO GOO

• ---------- ---------- 1.0E+000 1.0E+000 1

but, with a proper space:

    SQL> select 1. foo, 1. doo, 1. goo from dual;

           FOO DOO GOO

• ---------- ---------- 1 1 1

and more properly, with the AS keyword (which i strongly recommend as a best practice along with stacked formatting):

    SQL> select

      2    1. as foo
      3  , 1. as doo
      4  , 1. as goo
      5  from dual
      6  /

           FOO        DOO        GOO
    ---------- ---------- ----------
             1          1          1

but not recognized in 9iR2

    SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jan 26 08:00:38 2006

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production     ...
    SQL> select 1.foo, 1.doo, 1.goo from dual;

           FOO DOO GOO

• ---------- ---------- 1 1 1

so, a technical shame on me for altering you original example and using XXX instead of your original alias (of common usage but questionable etymology http://www.faqs.org/rfcs/rfc3092.html ;-)

++ mcs Received on Thu Jan 26 2006 - 07:15:59 CST