Home » SQL & PL/SQL » SQL & PL/SQL » sql query (oracle 9i, Forms 6i)
sql query [message #381155] Thu, 15 January 2009 08:05 Go to next message
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 #381166 is a reply to message #381155] Thu, 15 January 2009 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about telling the specifications in the form: I want all the rows that...
If you can say that then the query is most often easy to find.

Regards
Michel
Re: sql query [message #381171 is a reply to message #381166] Thu, 15 January 2009 09:15 Go to previous messageGo to next message
nasir_mughal
Messages: 122
Registered: April 2007
Location: Karachi
Senior Member
Please help me

Thanks
Re: sql query [message #381174 is a reply to message #381155] Thu, 15 January 2009 09:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>i want to write above block in single sql statement.
Why, when the code you have works so well?
Re: sql query [message #381177 is a reply to message #381174] Thu, 15 January 2009 09:36 Go to previous messageGo to next message
nasir_mughal
Messages: 122
Registered: April 2007
Location: Karachi
Senior Member
For performance because in real envirnment above cursor is taking too much time.
Re: sql query [message #381188 is a reply to message #381177] Thu, 15 January 2009 10:19 Go to previous messageGo to next message
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 #381193 is a reply to message #381188] Thu, 15 January 2009 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have no specification for this procedure?

Regards
Michel
Re: sql query [message #381274 is a reply to message #381193] Fri, 16 January 2009 01:51 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #381334 is a reply to message #381155] Fri, 16 January 2009 06:52 Go to previous messageGo to next message
mamalik
Messages: 268
Registered: November 2008
Location: Pakistan
Senior Member

Dear My Query is returning following. Its ok.

ITE ITEM_LABEL       QTY       VAL
--- ---------- --------- ---------
001 9122DS             5     69000
002 9144DS             1     14000
003 9166DS             8    121000
Re: sql query [message #381337 is a reply to message #381334] Fri, 16 January 2009 07:07 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
  • Attachment: Proof.jpg.JPG
    (Size: 100.97KB, Downloaded 446 times)
Re: sql query [message #381488 is a reply to message #381378] Sat, 17 January 2009 03:34 Go to previous message
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


Previous Topic: Anonymous Blocks
Next Topic: Urgent Help need -SQL
Goto Forum:
  


Current Time: Tue Nov 05 12:30:23 CST 2024