Solved

# Derived PL/SQL

Posted on 2004-11-05

Hello,

I want to put logic in my sql statement such that based on the data in one column, I multiply the value in another field by 1 or negative 1. For example, consider this data

Amount Credit Charge

--------------------------------------------------

100 0 1

200 0 1

250 1 0

145 0 1

Basically, it boils down to accounting: if the amount is a charge (meaning the credit is 0 and the charge is 1) then I just take the amount field. If the amount is a credit (meaning the Credit field is 1 and the charge field is 0), then I take the amount field *-1. This means that my amounts above would come out as follows:

100

200

-250

145

I know that if the field contained a NULL instead of 1 or 0, I could just use the NVL field to multiply the value by negative one, but I don't know how to do this based on a 0 or 1 being in another column. Any ideas? And I'm trying to just do this in one select statement withou having to build a procedure or anything.

Thanks!