| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Format A Calculated Column in a Query?
On Dec 1, 6:22 pm, "Jeff B" <jef..._at_KnoSpam.tds.net> wrote:
> "DA Morgan" <damor..._at_psoug.org> wrote in message
>
> news:1196547063.669257_at_bubbleator.drizzle.com...
> > Jeff B wrote:
> >> Good afternoon,
>
> >> I am try to figure out how to format a calculated column in a query but
> >> am not finding how to do it or if I can do it?
>
> >> Here is my Query and Results:
>
> >> SQL> Select RTRIM(Members.LastName), RTRIM(Members.FirstName),
> >> Position.Position, Campus.CampusName, (Position.YearlyMembershipFee / 12)
> >> AS Monthly_Dues
> >> 2 From Members, Position, Campus
> >> 3 Where Members.PositionID = Position.PositionID
> >> 4 And Members.CampusID = Campus.CampusID
> >> 5 Order by Campus.CampusName DESC, Members.LastName;
>
> >> RTRIM(MEMBERS.LASTNAME) RTRIM(MEMBERS.FIRSTN
> >> POSITION CAMPUSNAME MONTHLY_DUES
> >> -------------------------------------------------- --------------------
> >> ------------------------- ------------------------- ------------
> >> Bradley Wilson Associate
> >> Professor Purdue University 75.0416667
> >> Joe Brady Associate
> >> Professor Purdue University 75.0416667
> >> Sebastian Cole
> >> Full Professor Purdue University 41.7083333
> >> Dave Davidson Assistant
> >> Professor Indiana University 72.9583333
> >> Ellen Monk
> >> Full Professor Indiana University 41.7083333
> >> Michael Doo Lecturer
> >> Indiana University 87.5416667
> >> Bob House Professor
> >> IUPUI 58.3958333
> >> Bridget Stanley Lecturer
> >> IUPUI 87.5416667
> >> Jerome Clark Lecturer
> >> IUPUI 87.5416667
>
> >> My Column for Calculating Monthly dues are coming out with 7 places on
> >> the right of the decimal point but I would like that to only have 2. The
> >> YearlyMembershipFee column in the table was created as NUMBER(7,2) so I
> >> am guessing that that format does not carry to the new calculated field?
>
> >> Thanks for the help,
>
> >> Jeff
>
> > Alias the resulting columns
>
> > Select RTRIM(Members.LastName) AS LASTNAME, RTRIM(Members.FirstName) AS
> > FIRSTNAME
> > --
> > Daniel A. Morgan
> > Oracle Ace Director & Instructor
> > University of Washington
> > damor..._at_x.washington.edu (replace x with u to respond)
> > Puget Sound Oracle Users Group
> >www.psoug.org
>
> Hi Daniel,
>
> Thanks for the help. That will help with shortening down those two columns
> but what I am trying to figure out is how to get the Monthly_Dues column to
> format as like currency. so instead of the results in the column being
> 75.0416667 I would like for the results to be $75.04. Can I do this some
> how in my select statement? I did find a sort of workaround by before
> running the query is do a column format statement then ran the query and
> that did make the column come out the way I want it to look I am just
> wonderin if I can put it directly into my select statemnet?
>
> Thanks again,
>
> Jeff
The ROUND function can be used to round a value to two decimal places,
for example:
SELECT
ROUND(75.0416667,2)
FROM
DUAL;
ROUND(75.0416667,2)
75.04
The ROUND function will not cause the right most 0 digits to the right
of the decimal to print, as is common with your currency example. To
work around that, you might try using TO_CHAR:
SELECT
TO_CHAR(75.0416667,'$90.00')
FROM
DUAL;
TO_CHAR
SELECT
TO_CHAR(1175.0416667,'$90.00')
FROM
DUAL;
TO_CHAR
As you can see from the above, you need to provide enough "9"
characters to support the width of the output text. Repeating the
test with a modification:
SELECT
TO_CHAR(75.0416667,'$9,999,999,990.00')
FROM
DUAL;
TO_CHAR(75.0416667
$75.04
SELECT
TO_CHAR(1175.0416667,'$9,999,999,990.00')
FROM
DUAL;
TO_CHAR(1175.04166
$1,175.04
The above seems to have worked, but now we may have caused another
problem:
SELECT
LENGTH(TO_CHAR(1175.0416667,'$9,999,999,990.00')) LEN
FROM
DUAL;
LEN
18
The numeric output was left padded with spaces. Working around this:
SELECT
TRIM(TO_CHAR(1175.0416667,'$9,999,999,990.00'))
FROM
DUAL;
TRIM(TO_CHAR(1175.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Sat Dec 01 2007 - 19:34:29 CST
 |
 |