Re: Format A Calculated Column in a Query?

"DA Morgan" <damorgan_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
> damorgan_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 Received on Sat Dec 01 2007 - 17:22:41 CST