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: Jeff B <jeffby_at_KnoSpam.tds.net>
Date: Sun, 02 Dec 2007 04:23:00 GMT
Message-ID: <Eqq4j.40627$G23.3962@newsreading01.news.tds.net>

"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 Received on Sat Dec 01 2007 - 22:23:00 CST

Original text of this message

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