Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Format A Calculated Column in a Query?

Re: Format A Calculated Column in a Query?

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 02 Dec 2007 13:53:46 -0800
Message-ID: <1196632425.148151@bubbleator.drizzle.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US