Conditional replace statement

[JonathanHolliday]

Hi,

I'm looking for a simple statement to conditionally replace a value.

I have a value field that could be negative or positive depending on a Debit/Credit field.

Thus I need to query something like:

select {IF type = "debit" THEN value ELSE value * -1}
from table

Obviously the code in {} doesn't exist, is there such a code in informix without resorting to a procedure?

Yes, I'm new to Informix so forgive the fundamental question.

Thanks

Jonathan.

 

[vpshriyan]

Hi,

You may use the sql keyword DECODE to accomplish this.

create temp table tab1 (type char(6), value dec(10,2)) with no log;
insert into tab1 values ('credit',-1000.00);
insert into tab1 values ('credit',-2000.00);
insert into tab1 values ('debit ',500.00);
insert into tab1 values ('debit ',800.00);

select type,value,decode(type,"debit",value,value*-1) from tab1;

Regards,
Shriyan

"An ounce of example is worth a pound of advice."

 

[JonathanHolliday]

Shriyan,

Thank you, that's perfect. You're right about eg.s as well.

Regards,

Jonathan.