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)

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

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;

select * 
  from FlattenedTrades;

ITEM_CODE   PURCHASES      SALES
---------- ---------- ----------
ITEM1          100000      20000
ITEM2           20000      20000
ITEM3          101000     110000

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;

ITEM_CODE    QUANTITY TRADE_NATURE
---------- ---------- ------------
ITEM1           80000 Purchase
ITEM3            9000 Sale

This answer is continued.