Derived PL/SQL


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:


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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ccorrenteAuthor Commented:
Think I got it.  Can use the replace function to replace 0's in the Charge with -1.  

replace (string1, string_to_replace, [replacement_string] )

Then multiply the result of the replace function by the amount in my SQL statement.  If the Charge is 0 (meaning it's a credit) then the result of the replace will be -1 and thus multiplying the amount by -1 will be negative.  If the Charge is 1 (meaning it is a charge) then the replace returns a 1 and multiplying it just produces the same positive amount.
slightwv (䄆 Netminder) Commented:

select amount * decode(charge,1,-1,1) from table;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ccorrenteAuthor Commented:
Yeah, I like decode better.  Wasn't aware of that one.  I'll be sure to keep that one in mind in the future!
slightwv (䄆 Netminder) Commented:
It's the old style way of doing an if-then-else in Oracle.

FYI: for more complex queries, starting in 9i (I think.....I tend to get confused with all the NEW sql) Oracle introduced 'CASE' statements.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.