0

I am working on data in oracle 11g as below :

subscriber_id |  count_s  |  count_t |   min_count   |  diff    | negative_diff   |  positive_diff

137606401        3          3              3        0          0                  0
102842273        5          4              4        1          0                  1
116638321        14        14             14        0          0                  0
98353910         53        53             53        0          0                  0
102869643        18        20             18       -2          2                  0
107144617        21        21             21        0          0                  0

Can you please help me with a logic to update min_count , diff , negative_diff , positive_diff considering we have values for subcriber_id , count_s and count_t ?

here , min_count should be minimum of (count_s , count_t) for that specific subscriber_id.

diff should be difference between count_s and count_t , which can be easily attained with [count_s - count_t]

negative_diff should be updated for subscribers which has -ve diff values otherwise value for this should be 0 [-ve sign should not be considered while updating]

similarly , positive_diff should be updated for subscirbers which has +ve diff values otherwise value shouild be 0

please suggest the with sql code snippet to be used to update min_count , negative_diff and positive_diff.

Note: Working in SQL

  • Why is there a need to store these values in the table - and not just let Oracle calculate them every time you want them (with a query or using as VIEW, etc ...)? – ypercubeᵀᴹ Jul 23 '14 at 18:00
0
update t set
 min_count = least(count_s, count_t),
 diff = count_s - count_t,
 negative_diff = decode(sign(count_s - count_t), -1, count_s - count_t, 0),
 positive_diff = decode(sign(count_s - count_t),  1, count_s - count_t, 0)
;

Or just use virtual columns since you are on 11g:

create table t(
  subscriber_id number, 
  count_s number, 
  count_t number, 
  min_count as (least(count_s, count_t)),
  diff as (count_s - count_t), 
  negative_diff as (decode(sign(count_s - count_t), -1, count_s - count_t, 0)), 
  positive_diff as (decode(sign(count_s - count_t),  1, count_s - count_t, 0))
);
0

Check out following

update t
set min_count = least(count_s , count_t),
diff = (count_s - count_t),
negative_diff = case when (count_s - count_t) < 0 then abs(count_s - count_t) else 0 end,
positive_diff = case when (count_s - count_t) > 0 then count_s - count_t else 0 end

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.