Things I didn't know about Oracle column aliases

From: Norman Dunbar <oracle_at_dunbar-it.co.uk>
Date: Wed, 14 Dec 2011 09:44:50 +0000
Message-ID: <4EE87012.602_at_dunbar-it.co.uk>

I knew you could do this:

SQL> select 1234567890 as abc from dual;

        ABC

---

1234567890

or

SQL> select 1234567890 abc from dual;

        ABC

---

1234567890

But I didn't know that this worked as well:

SQL> select 1234567890abc from dual;

        ABC

---

1234567890

So I did a bit of playing and discovered that there is a difference if the alias is D or F but no other single character:

SQL> select 1234567890d, 1234567890f, 1234567890p from dual;

1234567890D 1234567890F P
----------- ----------- ----------

  1.235E+009 1.235E+009 1234567890

This shows the values in Scientific notation when D or F is used as an alias in this manner, but not if used in this manner:

SQL> select 1234567890 d, 1234567890 f, 1234567890 p from dual

          D F P
---------- ---------- ----------

1234567890 1234567890 1234567890

Then it gets stranger:

SQL> select 1234567890df from dual;

          F

---

1.235E+009

SQL> select 1234567890fd from dual

          D

---

1.235E+009

SQL> select 1234567890fa from dual

          A

---

1.235E+009

I get the impression that a trailing F or D on a number means "display as floating point or decimal" then the F/D is dropped and the A used as a label. I can't find this in the docs though.

Works with strings as well but the F/D thing doesn't appear with strings. Doesn't work - for obvious reasons - with column names.

Online also as
http://qdosmsq.dunbar-it.co.uk/blog/2011/12/slightly-weird-oracle-stuff/

Cheers,
Norm.

--

Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767
--

http://www.freelists.org/webpage/oracle-l Received on Wed Dec 14 2011 - 03:44:50 CST