It would very helpful for me if you can solve my problem. Right now I getting my required result after using two...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
'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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.