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


"Arto Viitanen" <arto.viitanen@xxxxxx> wrote in message
news:43d87dc3$0$10069$ba624cd0@xxxxxxxxxxxxxxxxxxxx
> 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


.

Relevant Pages

  • Re: is this bad?
    ... >1) the ';' character must mark the end of a statement. ... >query sort by the first column then end the statement. ... You could remove all ';' marks from any SQL script and SQL ... Server will still happily execute it. ...
    (microsoft.public.sqlserver.programming)
  • Re: Trigger & Sequence ++ show errors for triggers
    ... Mark C. Stock wrote: ... :> that SHOW ERRORS does not work on trigger compilation errors ... SQL> CREATE OR REPLACE TRIGGER statement_level ...
    (comp.databases.oracle.server)
  • Re: DataReader Seek
    ... and syntax-wise you are right on the mark. ... problem with CF sp3 and SQL CE 2.0, so I'd really like to look into this in detail. ... Darren Shaffer ...
    (microsoft.public.sqlserver.ce)
  • pop-up form to filter report
    ... Dim strSQL As String, intCounter As Integer ... 'Build SQL String ... >I meant to remove the question mark: ... when writing sql code use double quotes ...
    (microsoft.public.access.reports)
  • Re: How do I put % in a format sting?
    ... TypeError: not enough arguments for format string ... But I think SQL has other recommended methods. ... it is recommended you not use Python's %s formatter but instead the "?" ...
    (comp.lang.python)