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

x
Solved

# Derived PL/SQL

Posted on 2004-11-05
Medium Priority
1,100 Views
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!
0
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.
0

LVL 78

Accepted Solution

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

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

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!
0

LVL 78

Expert Comment

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.
0

## Featured Post

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