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 110000To 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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation