Things I didn't know about Oracle column aliases

From: Norman Dunbar <>
Date: Wed, 14 Dec 2011 09:44:50 +0000
Message-ID: <>

I knew you could do this:

SQL> select 1234567890 as abc from dual;




SQL> select 1234567890 abc from dual;



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

SQL> select 1234567890abc from dual;



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;



SQL> select 1234567890fd from dual



SQL> select 1234567890fa from dual



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



Norman Dunbar
Dunbar IT Consultants Ltd

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

Company Number: 05132767
-- Received on Wed Dec 14 2011 - 03:44:50 CST

Original text of this message