[Last Call] Learn how to a build a cloud-first strategyRegister Now


Derived PL/SQL

Posted on 2004-11-05
Medium Priority
Last Modified: 2010-05-18

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.

Question by:ccorrente
  • 2
  • 2

Author Comment

ID: 12508502
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.
LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 1000 total points
ID: 12508556

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

Author Comment

ID: 12508624
Yeah, I like decode better.  Wasn't aware of that one.  I'll be sure to keep that one in mind in the future!
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12508696
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.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month18 days, 6 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question