Jeff B wrote:
>
> "Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message
> news:1667da1b-87ac-4c17-b103-04dd844f855f_at_y5g2000hsf.googlegroups.com...
>> 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
>> -------
>> $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.
>
> 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
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_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Sun Dec 02 2007 - 15:53:46 CST
