Home » SQL & PL/SQL » SQL & PL/SQL » sql query (oracle 9i, Forms 6i)
sql query [message #381155] |
Thu, 15 January 2009 08:05 |
nasir_mughal
Messages: 122 Registered: April 2007 Location: Karachi
|
Senior Member |
|
|
Hi
My oracle database version is
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
i have following three tables and data
CREATE TABLE items (
item_id VARCHAR2(3),
item_label VARCHAR2(10))
CREATE TABLE stock (
item_id VARCHAR2(3),
op_qty NUMBER(4),
op_unit_cost NUMBER(10,2),
in_qty NUMBER(4),
out_qty NUMBER(4))
CREATE TABLE purchase (
voucher NUMBER(6),
v_date DATE,
item_id VARCHAR2(3),
qty NUMBER(4),
rate NUMBER(10,2))
insert into items values ('001','9122DS');
insert into items values ('002','9144DS');
insert into items values ('003','9166DS');
insert into stock values ('001',6,12000,6,7);
insert into stock values ('003',3,15000,6,1);
insert into stock values ('002',null,2,1);
insert into purchase values (1,to_date('01-04-2008','dd-mm-yyyy'),'001',2,12500);
insert into purchase values (2,to_date('06-04-2008','dd-mm-yyyy'),'003',1,15000);
insert into purchase values (3,to_date('07-04-2008','dd-mm-yyyy'),'002',2,14000);
insert into purchase values (4,to_date('10-04-2008','dd-mm-yyyy'),'001',3,13500);
insert into purchase values (5,to_date('12-04-2008','dd-mm-yyyy'),'001',1,13600);
insert into purchase values (6,to_date('15-04-2008','dd-mm-yyyy'),'003',5,15200);
The following block which first calculate the items balance in stock and looks for the cost of these balance items in "purchase" table by ordering the purchase vouchers in DESC order, if these items were not purchased then return op_unit_cost from "stock" table.
DECLARE
CURSOR c1 IS
SELECT b.item_label,
a.item_id,
Nvl(a.op_qty,0) + Nvl(a.in_qty,0) - Nvl(a.out_qty,0) balance
FROM stock a,
items b
WHERE a.item_id = b.item_id
AND Nvl(a.op_qty,0) + Nvl(a.in_qty,0) - Nvl(a.out_qty,0) > 0;
r1 c1%ROWTYPE;
item_cost NUMBER(12,2) := 0;
op_u_cost NUMBER(12,2) := 0;
op_cost NUMBER(12,2) := 0;
bal_qty NUMBER(4) := 0;
BEGIN
IF NOT c1%ISOPEN THEN
OPEN c1;
END IF;
LOOP
FETCH c1 INTO r1;
EXIT WHEN c1%NOTFOUND;
op_cost := 0;
op_u_cost := 0;
item_cost := 0;
bal_qty := r1.balance;
DECLARE
CURSOR c2 IS
SELECT *
FROM purchase
WHERE item_id = r1.item_id
ORDER BY v_date DESC,
ROWNUM DESC;
r2 c2%ROWTYPE;
BEGIN
IF NOT c2%ISOPEN THEN
OPEN c2;
END IF;
LOOP
FETCH c2 INTO r2;
EXIT WHEN c2%NOTFOUND
OR bal_qty = 0;
IF r2.qty < bal_qty THEN
item_cost := item_cost + r2.qty * Nvl(r2.rate,0);
bal_qty := bal_qty - Nvl(r2.qty,0);
ELSE
item_cost := item_cost + bal_qty * Nvl(r2.rate,0);
bal_qty := bal_qty - Nvl(r2.qty,0);
EXIT;
END IF;
END LOOP;
CLOSE c2;
END;
IF bal_qty > 0 THEN
SELECT op_unit_cost
INTO op_u_cost
FROM stock
WHERE item_id = r1.item_id;
END IF;
op_cost := bal_qty * op_u_cost;
item_cost := item_cost + op_cost;
dbms_output.Put_line(r1.item_label
||' - '
||r1.item_id
||' - '
||r1.balance
||' - '
||item_cost);
END LOOP;
END;
/
SQL> /
9122DS - 001 - 5 - 66600
9144DS - 002 - 1 - 14000
9166DS - 003 - 8 - 121000
i want to write above block in single sql statement.
Kindly help me.
Thanks.
|
|
|
|
|
|
|
Re: sql query [message #381188 is a reply to message #381177] |
Thu, 15 January 2009 10:19 |
nasir_mughal
Messages: 122 Registered: April 2007 Location: Karachi
|
Senior Member |
|
|
with this query i can find items balance in stock
SQL> select a.item_id,b.item_label,nvl(a.op_qty,0)+nvl(a.in_qty,0)-nvl(a.out_qty,0) balance
2 from stock a,items b
3 where a.item_id=b.item_id
4 /
ITE ITEM_LABEL BALANCE
--- ---------- ----------
001 9122DS 5
002 9144DS 1
003 9166DS 8
order purchase table on v_date in DESC order
SQL> select * from purchase
2 order by item_id,v_date desc
3 /
VOUCHER V_DATE ITE QTY RATE
---------- --------- --- ---------- ----------
5 12-APR-08 001 1 13600
4 10-APR-08 001 3 13500
1 01-APR-08 001 2 12500
3 07-APR-08 002 2 14000
6 15-APR-08 003 5 15200
2 06-APR-08 003 1 15000
1) i have to look for (5) "9122DS" in purchase table.
-first row has (1) in qty column
-second row has (3) in qty colum so 3 X 13500
-third row has (2) in qty colum,
from third row (1) will be selected to make (5) "9122DS"
2) for (1) 9144DS
-forth row has (2) in qty colum
only (1) will be selected to make (1) "9144DS"
3) for 8 9166DS
-fifth row has (5) in qty column
-sixth row has (1) in qty column
since there are not any more rows and still there (2) 9166DS left,therefor (2) items should be select from stock in op_qty
now i have to look for (2)
helpe me thanks
|
|
|
|
Re: sql query [message #381274 is a reply to message #381193] |
Fri, 16 January 2009 01:51 |
nasir_mughal
Messages: 122 Registered: April 2007 Location: Karachi
|
Senior Member |
|
|
-In upper procedure cursor "C1" calculates the items balance in stock
-"item_id" and "balance" from "C1" is passed to create the cursor "C2" which select rows from pruchase table for that item after odering on v_date DESC
-This loop loops through rows from "purchase" table to calculate the cost of items balance in stock.
LOOP
FETCH c2 INTO r2;
EXIT WHEN c2%NOTFOUND
OR bal_qty = 0;
IF r2.qty < bal_qty THEN
item_cost := item_cost + r2.qty * Nvl(r2.rate,0);
bal_qty := bal_qty - Nvl(r2.qty,0);
ELSE
item_cost := item_cost + bal_qty * Nvl(r2.rate,0);
bal_qty := bal_qty - Nvl(r2.qty,0);
EXIT;
END IF;
END LOOP;
if there are not any more rows and balance is still not zero
then this part of procedure gets those remaining items from "stock" table
IF bal_qty > 0 THEN
SELECT op_unit_cost
INTO op_u_cost
FROM stock
WHERE item_id = r1.item_id;
END IF;
op_cost := bal_qty * op_u_cost;
item_cost := item_cost + op_cost;
Kindly help me
Thanks
|
|
|
Re: sql query [message #381321 is a reply to message #381155] |
Fri, 16 January 2009 06:06 |
mamalik
Messages: 268 Registered: November 2008 Location: Pakistan
|
Senior Member |
|
|
Try This
SELECT stk_tbl.item_id, item_label,
(balance - SUM (itm_qty)) + SUM (itm_qty) qty,
SUM (itm_val) + ((balance - SUM (itm_qty)) * op_unit_cost) val
FROM (SELECT v_date, item_id, balance, op_unit_cost,
CASE
WHEN bal > 0
THEN qty * rate
WHEN bal < 0
THEN CASE
WHEN (LAG (bal, 1, 0) OVER (PARTITION BY item_id ORDER BY item_id,
v_date)
) <= 0
THEN (balance * rate)
ELSE ( LAG (bal, 1, 0) OVER (PARTITION BY item_id ORDER BY item_id,
v_date)
* rate
)
END
WHEN bal = 0
THEN 0
END itm_val,
CASE
WHEN bal > 0
THEN qty
WHEN bal < 0
THEN CASE
WHEN (LAG (bal, 1, 0) OVER (PARTITION BY item_id ORDER BY item_id,
v_date)
) <= 0
THEN balance
ELSE (LAG (bal, 1, 0) OVER (PARTITION BY item_id ORDER BY item_id,
v_date)
)
END
WHEN bal = 0
THEN 0
END itm_qty
FROM (SELECT v_date, item_id, balance, qty, rate, op_unit_cost,
balance
- ( qty
+ prv_qty
+ (LAG (prv_qty, 1, 0) OVER (PARTITION BY item_id ORDER BY item_id,
v_date)
)
) bal
FROM (SELECT v_date, a.item_id,
NVL (a.op_qty, 0)
+ NVL (a.in_qty, 0)
- NVL (a.out_qty, 0) balance,
op_unit_cost, qty, rate,
LAG (qty, 1, 0) OVER (PARTITION BY a.item_id ORDER BY p.item_id,
v_date, ROWNUM) prv_qty,
ROWNUM
FROM stock a, purchase p
WHERE NVL (a.op_qty, 0)
+ NVL (a.in_qty, 0)
- NVL (a.out_qty, 0) > 0
AND p.item_id(+) = a.item_id
ORDER BY item_id, p.v_date, p.ROWID)
ORDER BY item_id, v_date, ROWNUM)) stk_tbl,
items b
WHERE stk_tbl.item_id = b.item_id
GROUP BY stk_tbl.item_id, item_label, balance, op_unit_cost
ORDER BY item_id
Regards
Muhammad Asif Malik.
|
|
|
Re: sql query [message #381333 is a reply to message #381321] |
Fri, 16 January 2009 06:46 |
nasir_mughal
Messages: 122 Registered: April 2007 Location: Karachi
|
Senior Member |
|
|
Thanks a lot for your time.
but on executing your query is giving wrong result.
65 /
ITE ITEM_LABEL QTY VAL
--- ---------- ---------- ----------
001 9122DS 5 69000
002 9144DS 1
003 9166DS 8 121000
the result of the query should be
70 /
9122DS - 001 - 5 - 66600
9144DS - 002 - 1 - 14000
9166DS - 003 - 8 - 121000
PL/SQL procedure successfully completed.
|
|
|
|
Re: sql query [message #381337 is a reply to message #381334] |
Fri, 16 January 2009 07:07 |
nasir_mughal
Messages: 122 Registered: April 2007 Location: Karachi
|
Senior Member |
|
|
The same query i am running which you have provided
but its giving different result how it is possible?
here is the sql session displaying result of your query.
65 /
ITE ITEM_LABEL QTY VAL
--- ---------- ---------- ----------
001 9122DS 5 69000
002 9144DS 1
003 9166DS 8 121000
|
|
|
Re: sql query [message #381378 is a reply to message #381155] |
Fri, 16 January 2009 09:08 |
mamalik
Messages: 268 Registered: November 2008 Location: Pakistan
|
Senior Member |
|
|
Now Try This.
select Stk_Tbl.item_id,Item_label,balance,sum(itm_val)+((balance-sum(itm_qty))*op_unit_cost) val from(
SELECT v_date, item_id, balance, op_unit_cost,bal,
CASE
WHEN bal > 0
THEN qty
WHEN bal < 0
THEN CASE
WHEN (LAG (bal, 1, 0) OVER (PARTITION BY item_id ORDER BY item_id,
v_date desc)
) <= 0
THEN balance
ELSE (LAG (bal, 1, 0) OVER (PARTITION BY item_id ORDER BY item_id,
v_date desc)
)
END
WHEN bal = 0
THEN 0
END itm_qty,
CASE
WHEN bal > 0
THEN qty * rate
WHEN bal < 0
THEN CASE
WHEN (LAG (bal, 1, 0) OVER (PARTITION BY item_id ORDER BY item_id,
v_date desc)
) <= 0
THEN (balance * rate)
ELSE ( LAG (bal, 1, 0) OVER (PARTITION BY item_id ORDER BY item_id,
v_date desc)
* rate
)
END
WHEN bal = 0
THEN 0
END itm_val
FROM (
SELECT v_date, item_id, balance, qty, rate, op_unit_cost,
balance
- ( qty
+ prv_qty
+ (LAG (prv_qty, 1, 0) OVER (PARTITION BY item_id ORDER BY item_id,
v_date desc)
)
) bal
FROM (SELECT v_date, a.item_id,
NVL (a.op_qty, 0)
+ NVL (a.in_qty, 0)
- NVL (a.out_qty, 0) balance,
op_unit_cost, qty, rate,
LAG (qty, 1, 0) OVER (PARTITION BY a.item_id ORDER BY p.item_id,
v_date desc) prv_qty,
ROWNUM
FROM stock a, purchase p
WHERE NVL (a.op_qty, 0)
+ NVL (a.in_qty, 0)
- NVL (a.out_qty, 0) > 0
AND p.item_id(+) = a.item_id
ORDER BY item_id, p.v_date desc)
ORDER BY item_id, v_date desc)
ORDER BY item_id, v_date desc) stk_tbl,Items Itm_Tbl
Where Stk_Tbl.Item_id=Itm_Tbl.Item_id
group by Stk_tbl.item_id,balance,op_unit_cost,Item_label
Result is
ITE ITEM_LABEL BALANCE VAL
--- ---------- --------- ---------
001 9122DS 5 66600
002 9144DS 1 14000
003 9166DS 8 121000
Now you can see attached file to confirm that now my query is returning right result.
Best Regards.
Muhammad Asif Malik.
|
|
|
Re: sql query [message #381488 is a reply to message #381378] |
Sat, 17 January 2009 03:34 |
nasir_mughal
Messages: 122 Registered: April 2007 Location: Karachi
|
Senior Member |
|
|
i am not sure why your query is still giving wrong result
maybe there is a version difference
however i have solved my problem by
SQL> create or replace view pur_Data
2 as select * from (select rownum rn,item_id,v_date,qty,rate
3 from purchase
4 union all
5 select rownum rn,item_id,null,op_qty qty,op_unit_cost rate
6 from stock)
7 /
View created.
SQL> select a.item_id,a.item_label,sum(b.qty),sum(item_cost)
2 from items a,
3 (select a.item_id,a.rate,
4 case when cum_sum > b.bal_qty
5 then
6 b.bal_qty - (cum_sum - a.qty)
7 else
8 a.qty
9 end qty,
10 case when cum_sum > b.bal_qty
11 then
12 ( b.bal_qty - (cum_sum - a.qty )) * a.rate
13 else
14 a.qty * a.rate
15 end item_cost
16 from
17 (
18 select item_id, rate, qty,
19 sum(qty) over(partition by item_id
20 order by v_date desc nulls last) cum_sum
21 from pur_data
22 ) a, ( select item_id,nvl(op_qty,0)+nvl(in_qty,0)-nvl(out_qty,0) bal_qty from stock
23 where nvl(op_qty,0)+nvl(in_qty,0)-nvl(out_qty,0)>0) b
24 where a.item_id=b.item_id and (cum_sum - qty) <= b.bal_qty) b
25 where a.item_id=b.item_id
26 group by a.item_id,a.item_label
27 /
ITE ITEM_LABEL SUM(B.QTY) SUM(ITEM_COST)
--- ---------- ---------- --------------
001 9122DS 5 66600
002 9144DS 1 14000
003 9166DS 8 121000
Thanks for your help
|
|
|
Goto Forum:
Current Time: Tue Nov 05 12:30:23 CST 2024
|