Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    2

    Unanswered: Update with case

    I have a problem in an update, i need to update one coloum with a case condition that involves more than 1 case. The current sql statement i came up with goes like this.

    update FMS_DATA
    set valueDKKTEMP = CASE exchange.rates when 0 then fms_data.value * 1 else exchange.rates * fms_data.value end

    FROM HFO_Account RIGHT OUTER JOIN
    FMS_DATA ON HFO_Account.CHILD = FMS_DATA.ACCOUNT LEFT OUTER JOIN
    exchange ON HFO_Account.EXCHTYPE = exchange.TYPE AND FMS_DATA.CURRENCY = exchange._NAME_ AND
    FMS_DATA.TIME = exchange.TIME AND FMS_DATA.ANALYSIS = exchange.ANALYSIS

    but i also need to check for the currency being = dkk like this

    set valueDKKTEMP CASE fms_data.currency WHEN 'dkk' THEN fms_data.value * 1

    how do i do this?

    thx in advance

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Doesn't it work as an ordinary UPDATE statement? That is,

    UPDATE your_table SET
    column_1 = some_value,
    column_2 = another_value
    WHERE condition;

    some_value would be your exchange.rates CASE, and another_value would then be your fms_data.currency CASE.

  3. #3
    Join Date
    Feb 2005
    Posts
    2
    i only need to update the one coloum based on several conditions, the thing i want to do is

    update FMS_DATA
    set valueDKKTEMP = CASE exchange.rates when 0 then fms_data.value * 1 when fms_data.currency = 'dkk' THEN fms_data.value * 1
    else exchange.rates * fms_data.value end

    the above is incorrect i know, but this is what i need to do.

    Because i must make sure the curency isn't 'dkk' and that exchange.rates isn't 0 and in all other cases exchange.rates * fms_data.value.

    so actually i need 2 case to update 1 coloum.

    thx

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I see ... sorry, didn't read your initial question carefully enough.

    How about using the DECODE function? Maybe it'll help ...
    Code:
    UPDATE fms_data
       SET valuedkktemp =
              (SELECT DECODE (exchange.rates,
                              0, fms_data.value,
                                 DECODE (fms_data.currency, 'dkk', fms_data.value, exchange.rates * fms_data.value)
                 FROM exchange, fms_data
                 WHERE ...)
    WHERE ...;
    And another suggestion ... watching this, perhaps you could use concatenation:
    Code:
    UPDATE fms_data
       SET valuedkktemp =
              (SELECT DECODE (to_char(exchange.rates) || fms_data.currency,
                              '0dkk', fms_data.value,
                                      exchange.rates * fms_data.value
                             )
                 FROM exchange, fms_data
                 WHERE ...)
    WHERE ...;
    And, then again, concatenated "rates and currency" could be used in the CASE statement ... couldn't they?
    Last edited by Littlefoot; 02-14-05 at 02:28.

Posting Permissions

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