Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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
but, with a proper space:
SQL> select 1. foo, 1. doo, 1. goo from dual;
FOO DOO GOO
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
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