Select only negative values

[SteveCulshaw]

Is there any "elegant" way to select only the negative values from a number of fields ?
i.e. I have a table in which the users are entering values for the number of properties gained and lost, and I need to report on the total number of properties gained and lost, so I somehow need to sum only the positive numbers, and sum only the negative numbers for all fields
- I suppose I'm thinking of some sort of "filter" where I can include/exclude by a parameter

I was looking at using CASE to pull out the values, but there are a lot of fields, and wondered if there is some "set function" in Oracle 8i that does this Cheers,
Steve C.

 

[lewisp]

Is something like this what you need?

SELECT
(select sum(field1)
from table
where field1 < 0 ) losses,
(select sum(field1)
from table
where field1 > 0) gains
from sys.dual;

 

[SteveCulshaw]

Thanks, that looks good Cheers,
Steve C.

 

[sem]

select sum(decode(sign(field),1, field,0)) gains,
sum(decode(sign(field),-1, field, 0)) losses
from table

I think that this query is more efficient Regards, Dima