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

Jeff B wrote:
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
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

"DA Morgan" <damorgan (AT) psoug (DOT) org> wrote

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

On Dec 1, 6:22 pm, "Jeff B" <jef... (AT) KnoSpam (DOT) tds.net> wrote:
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
-------
$75.04

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.
------------------
$1,175.04

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

"Charles Hooper" <hooperc2000 (AT) yahoo (DOT) com> wrote

Thanks charles that gives me something to go on. I think the format
,$9,999,999,990.99 in the parenthsis right behind the value is probably what
i am looking for. will have to experiment with that.

Thanks for the input and help.

Jeff

Jeff B wrote:
You will find all of the various formating options documented at:
http://tahiti.oracle.com
and live demos of the most commonly used at:
http://www.psoug.org/library.html
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Dec 1, 3:22 pm, "Jeff B" <jef... (AT) KnoSpam (DOT) tds.net> wrote:
There are also some examples of how to format things if you search for
the format command in the standard directories:
grep format $ORACLE_HOME/*/*/*.sql|grep 999|more
(or whatever your favorite search tool command is).

Then look at some of those sqls.

jg
--
@home.com is bogus.
http://freakonomics.blogs.nytimes.co...our-questions/