Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table named as transaction,in that table trans_type column is there.I want to check the trans_type like bellow

if(trans_type = 'AR','AD','AF','AG') then
trans_type:='A';
elsif trans_type = 'BA','BS','BD','BG' then
trans_type:='B';
end if;

How to write this one by using decode function? Kindly tell me answer.

share|improve this question
    
Surely you tried something yourself? What didn't work? –  Colin 't Hart May 19 at 9:47
    
select sr_cd,to_char(trunc(transdate,'mm'),'YYYYMM') Month,sum(quantity) Receipt from transdetail where transtype IN('STKREC','RP','RECADJ','SRET') and trunc(transdate,'mm') between '01-April-2013' and '31-March-2014' group by trunc(transdate,'mm'),sr_cd; –  Tamil May 19 at 9:58
    
I want to update one column with sum(qty) based on trans_type('AR','AD') and update another column with sum(qty) based on trans_type('BA','BS') –  Tamil May 19 at 10:09

2 Answers 2

Note that for some cases, an if statement can be turned into a case expression which is somewhat similar to the ? operator in C and Java, but more generic. Your pseudo-code:

if(trans_type = 'AR','AD','AF','AG') then
trans_type:='A';
elsif trans_type = 'BA','BS','BD','BG' then
trans_type:='B';
end if;

becomes:

case
  when trans_type in ('AR','AD','AF','AG') then 'A'
  when trans_type in ('BA','BS','BD','BG') then 'B'
end case

as a case expression.

share|improve this answer
trans_type = DECODE(trans_type, 'AR', 'A', 'AD', 'A', 'AF', 'A', 'AG', 'A'
                  ,'BA', 'B', 'BS', 'B', 'BD', 'B', 'BG', 'B')

or more easily, if those are all the values for the field

trans_type = substr(trans_type, 1, 1)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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