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

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

    Hello,

    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
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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
    Code:
    create view celsius_fahrenheit as
    select 
      room_id, 
      celsius_temperature,
      celsius_temperature * 9 / 5 + 32 fahrenheit_temperature
    from your_table;
    If you insist on another column: first alter the table, then update it:
    Code:
    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
    Posts
    59
    Provided Answers: 1
    Thank you for your detailed solutions.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    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:
    Code:
    ALTER TABLE tblroomtemp
      ADD ( fahrenheit_temperature AS ( celsius_temperature * (9/5) + 32 )) )
    Last edited by LKBrwn_DBA; 04-02-12 at 18: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
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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
  •