Ask the Expert

Using CASE to obtain conditional SUMs

It would very helpful for me if you can solve my problem. Right now I getting my required result after using two 'cursor loops,' which are creating a performance problem in my software package.

Table and Data:

Table 1

create table trade
  (trade_id varchar2(10) primary key, 
   item_code varchar2(10) ,
   quantity number(10) ,
   sale_purchase char(1)); 
/* 'S' for Sale and 'P' for Purchase */

insert into trade values ('0001','ITEM1',10000,'S');
insert into trade values ('0002','ITEM1',100000,'P');
insert into trade values ('0003','ITEM1',10000,'S');
insert into trade values ('0004','ITEM2',20000,'S');
insert into trade values ('0005','ITEM2',20000,'P');
insert into trade values ('0006','ITEM3',100000,'P');
insert into trade values ('0007','ITEM3',100000,'S');
insert into trade values ('0008','ITEM3',10000,'S');
insert into trade values ('0009','ITEM3',1000,'P');

TABLE.......2

Create table trade_back
  (item_code varchar2(10),
   quantity  number(10),
   trade_nature  varchar2(100) check trade_nature in
('SOLD','PURCHASED','SETTLED'));

Required Conditions

trade_nature = 'PURCHASED' when an item 
(total purchase - total sale)
give +ve response

trade_nature = 'SALE' when an item 
(total purchase - total sale)
give -ve response.

trade_nature = 'SETTLED' give the result of 
least(total_purchase,total_sale)
I just want to write a procedure or SQL query that will insert the results of specific items in table 2.
Desired Result. (select * from trade_back);

item_code	Quantity	Trade_Nature

item1		80000		Purchase
item1		20000		Settled
item2		20000		Settled
item3		9000		Sale
item3		101000		Settled


    Requires Free Membership to View

Your desired result seems to be a combination of two different views of the same information. The first is the difference between total Sales and total Purchases with a Trade_Nature column to indicate which is larger. The second portion is a Settled row indicating the least of total Sales and total Purchases. I'll demonstrate ways to get each result, and then I'll demonstrate a way to combine them into one query to get one result set.

We can do this easily without using SQL Views, but I've opted to use them because it illustrates the solution in a nice way. First, we'll create a flattened view of the original table with one row for each item with a column for Purchase and a column for Sales. This is very easy when we use the CASE statement. I noticed you were using Oracle data types for the columns of your table. If you are using a pre-8i version of Oracle, you'll need to use the DECODE function to accomplish the same thing. CASE is supported by MS SQL Server and should be supported by other popular databases. Here's our view:

create view FlattenedTrades
as
select Item_Code, 
       sum( Quantity * 
            case Sale_Purchase 
              when 'P' then 1 
              when 'S' then 0 
            end ) Purchases,
       sum( Quantity * 
            case Sale_Purchase 
              when 'P' then 0 
              when 'S' then 1 
            end ) Sales
  from Trade
  group by Item_Code;
Please note the use of the CASE function to obtain conditional SUMs. Here is what is returned when we select from the view:
select * 
  from FlattenedTrades;

ITEM_CODE   PURCHASES      SALES
---------- ---------- ----------
ITEM1          100000      20000
ITEM2           20000      20000
ITEM3          101000     110000
To get the first part of our query, can use the CASE statement again to get our Trade_Nature. ABS gives us the absolute value of the difference. Incidentally, when taking the absolute value of the difference of two numbers, the order doesn't matter. Here's the SQL:
select Item_Code, 
       abs(Purchases - Sales) Quantity,
       case sign(Purchases - Sales)
         when  1 then 'Purchase'
         when -1 then 'Sale'
       end Trade_Nature
  from FlattenedTrades
  where Purchases != Sales;
For the first portion of our query, the results are exactly what we're looking for:
ITEM_CODE    QUANTITY TRADE_NATURE
---------- ---------- ------------
ITEM1           80000 Purchase
ITEM3            9000 Sale

This answer is continued.


This was first published in March 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: