Things I didn't know about Oracle column aliases
- From: Norman Dunbar <oracle@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 14 Dec 2011 09:44:50 +0000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
