Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Provided Answers: 1

    Unanswered: Add new column in table for convert Celsius to Fahrenheit at run time


    I have a table named tblRoomTemp that stored data as following:

    Room_ID | Celsius_Temperature
    1 | 32
    2 | 65
    3 | 99
    4 | 120
    5 | 67

    I like to add another column named Fahrenheit_Temperature to above table, then convert the current Celsius_Temperature data to Fahrenheit_Temperature by using the following formula: F = C * (9/5) + 32

    Is it possible to write PL/SQL query to run and get the result as bellowed:

    Room_ID | Celsius_Temperature | Fahrenheit_Temperature |
    1 | 32 | ?
    2 | 65 | ?
    3 | 99 | ?

    Thanks in advance.

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    Is it possible? Yes.

    However, a few objections: why would you want to STORE values that can easily be calculated at any time? This is similar to storing numbers in a table, and then - in another column - storing the same value multiplied by 5. Pointless. You can display Fahrenheits to end users any time, anyway.

    Furthermore, why PL/SQL code?!? SQL is more than capable in doing the same job; probably, more efficiently than PL/SQL.

    An option could be creating a view; something like
    create view celsius_fahrenheit as
      celsius_temperature * 9 / 5 + 32 fahrenheit_temperature
    from your_table;
    If you insist on another column: first alter the table, then update it:
    alter table your_table add fahrenheit_temperature number;
    update your_table set
      fahrenheit_temperature = celsius_temperature * 9 / 5 + 32;
    Any new record you insert, any update you make, should take care about the fact that you need to maintain TWO values instead of just one. Are you sure you want to do that? How? Fix existing code? Are you sure you won't miss something? Will you create a database trigger that will do the job?

    As I said: the whole operation is pointless. Maybe the best option is to do nothing; the second best might be a view. Everything else is questionable.

  3. #3
    Join Date
    Dec 2005
    Provided Answers: 1
    Thank you for your detailed solutions.

  4. #4
    Join Date
    Jun 2003
    West Palm Beach, FL
    Quote Originally Posted by avt2k6 View Post
    ... Etc ...
    I like to add another column named Fahrenheit_Temperature to above table,
    ... etc ....
    If you have Oracle version 11g, you can add it permanently to the tableas "virtual" column:
    ALTER TABLE tblroomtemp
      ADD ( fahrenheit_temperature AS ( celsius_temperature * (9/5) + 32 )) )
    Last edited by LKBrwn_DBA; 04-02-12 at 17:16. Reason: added "virtual"
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Jan 2003
    Provided Answers: 17
    That depends on how the assignment was worded.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts