How to use decode to determine if number is negative 1

[golyg]

I have a view that I'm using for CR report. I am using a decode statement so that I can determine which range the specific amount falls in. Just recently, I have come across the need to decode negative numbers. My questions are, how can I include negative numbers correctly? Is there a better way?

decode( trunc((decode(s_fees, NULL, 0, s_fees) / 50000)), -1, '<0', 0, ' 0 - 50K', 1, ' 50 - 100K', 2, '100 - 150K', 3, '150 - 200K', 4, '200 - 250K', '> 250K') settlement_amt_interval,

As you can see, if the negative number is greater than -1 but less than 0....I'm not picking it up and it falls under the >250K field..
I would like to somehow assign all negative numbers = -50000 so that I can determine for sure if its negative.

thanks

 

[carp]

I would use the SIGN function. If the result is -1, then you have a negative number. If the result is 0, then your number is zero. If the result is 1, then you have a positive number.

This will require another nested DECODE, but will give cover all negative values.

Elbert, CO
0934 MDT

 

[sem]

And as we're in 9i forum you may use CASE clause.

Regards, Dima

http://seminihin.narod.ru

 

[golyg]

Perfect. that SIGN() was the key..

Thanks!