Delete Records [merged 3] [message #583447] |
Wed, 01 May 2013 02:10 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mdamjad
Messages: 45 Registered: May 2005
|
Member |
|
|
![/forum/fa/10775/0/](/forum/fa/10775/0/)
Dear members ,
Sqty is sold qty , rqty is return qty , sdate is sold date , vno is billno .
When we enter sdate , this will fetch the products sold on the given date , and total sold qty .
If some products are returned which are entered in rqty , then this should delete all the records
of the product on that sdate , and these products as spread along several billno's .
Please help with code .
Thanks
-
Attachment: cash.JPG
(Size: 13.49KB, Downloaded 1485 times)
|
|
|
Re: Delete records [message #583450 is a reply to message #583447] |
Wed, 01 May 2013 02:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
We don't have you tables, or your data, or a full description of the logic you want to apply.
We don't know if the datablock is based on a table or at what exact point in the process you want the delete to be run.
So we really can't offer any help without more details from you.
|
|
|
|
Delete Records [message #583583 is a reply to message #583447] |
Thu, 02 May 2013 00:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mdamjad
Messages: 45 Registered: May 2005
|
Member |
|
|
Dear members ,
Sqty is sold qty , rqty is return qty , sdate is sold date , vno is billno .
When we enter sdate , this will fetch the products sold on the given date , and total sold qty .
If some products are returned which are entered in rqty , then this should delete all the records
of the product on that sdate , and these products as spread along several billno's .
Table name : cash_detail , logic should be processed at WHEN_BUTTON_PRESSED
Please help with code .
-
Attachment: cash.JPG
(Size: 13.49KB, Downloaded 1443 times)
|
|
|
|
Delete Records [message #583614 is a reply to message #583447] |
Thu, 02 May 2013 05:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mdamjad
Messages: 45 Registered: May 2005
|
Member |
|
|
Table Name : cash_detail
VNO Sdate Prod_code Sqty
1 30-04-2013 P001 1
2 30-04-2013 P001 2
3 30-04-2013 P001 5
9 30-01-2013 P001 4
12 30-04-2013 P001 8
I am want to give return_qty(number) say 17 , this should delete
vno= 1,2,3,9 and update vno 12 sqty to 3
ie sum of sqty=20 - return_qty =17 , So sqty should remain 3 for vno=12 .
|
|
|
|
Re: Delete Records [message #583617 is a reply to message #583616] |
Thu, 02 May 2013 06:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mdamjad
Messages: 45 Registered: May 2005
|
Member |
|
|
CREATE TABLE CASH_DETAIL
(VNO NUMBER(3) PRIMARY KEY ,
SDATE DATE ,
PROD_CODE VARCHAR2(10),
SQTY NUMBER(3)
);
INSERT INTO CASH_DETAIL VALUES(1,'30-04-2013','P001',1);
INSERT INTO CASH_DETAIL VALUES(2,'30-04-2013','P001',2);
INSERT INTO CASH_DETAIL VALUES(3,'30-04-2013','P001',5);
INSERT INTO CASH_DETAIL VALUES(9,'30-04-2013','P001',4);
INSERT INTO CASH_DETAIL VALUES(12,'30-04-2013','P001',8);
I am want to give return_qty(number) say 17 , this should delete
vno= 1,2,3,9 and update vno 12 sqty to 3
ie sum of sqty=20 - return_qty =17 , So sqty should remain 3 for vno=12 .
|
|
|
|
|
Re: Delete Records [message #583620 is a reply to message #583619] |
Thu, 02 May 2013 06:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mdamjad
Messages: 45 Registered: May 2005
|
Member |
|
|
Delete from cash_Detail where sdate='30-04-2013' and prod_code='P001',
This will delete all the records of sdate='30-04-2013' for P001 ,
but I want to delete first 4 records and update the last one to 3 .
Regards
Amjad
|
|
|
|
Re: Delete Records [message #583625 is a reply to message #583620] |
Thu, 02 May 2013 07:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
mdamjad wrote on Thu, 02 May 2013 07:44
but I want to delete first 4 records and update the last one to 3 .
Why? And don't just repeat the same unhelpful explanation.
|
|
|
Re: Delete records [message #583626 is a reply to message #583594] |
Thu, 02 May 2013 08:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mdamjad
Messages: 45 Registered: May 2005
|
Member |
|
|
Table Name : cash_detail
VNO Sdate Prod_code Sqty
1 30-04-2013 P001 1
2 30-04-2013 P001 2
3 30-04-2013 P001 5
9 30-01-2013 P001 4
12 30-04-2013 P001 8
I am want to give return_qty(number) say 17 , this should delete
vno= 1,2,3,9 and update vno 12 sqty to 3
ie sum of sqty=20 - return_qty =17 , So sqty should remain 3 for vno=12 .
|
|
|
Re: Delete records [message #583630 is a reply to message #583626] |
Thu, 02 May 2013 08:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
I think I might get it.
It appears to be a FIFO question (ordered by VNO). I'm too tired to consider the answer but hopefully the explanation is a)right and b) helps some others.
|
|
|
|
Re: Delete records [message #583688 is a reply to message #583643] |
Fri, 03 May 2013 00:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mdamjad
Messages: 45 Registered: May 2005
|
Member |
|
|
Thank you .
Suppose I call the procedure by passing the following argument
SQL> exec p_cash('P001', 4);
the procedure should delete vno=1 and vno=2 , and update vno=3 , sqty as 4
sample output after calling procedure .
VNO SDATE PROD SQTY
---------- ---------- ---- ----------
3 30.04.2013 P001 4
9 30.04.2013 P001 4
12 30.04.2013 P001 8
|
|
|
|
Re: Delete records [message #583828 is a reply to message #583726] |
Sun, 05 May 2013 06:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mdamjad
Messages: 45 Registered: May 2005
|
Member |
|
|
I have changed the code now the procedure is deleting records
create or replace procedure p_cash (par_code in varchar2,
par_rqty in number)
is
begin
for cur_r in (select vno, sqty
from cash_detail
where prod_code = par_code
order by vno)
loop
IF cur_r.sqty<=par_rqty then
delete from cash_detail
where vno = cur_r.vno;
end if;
end loop;
end p_cash;
for example if I call the procedure as below
SQL>EXEC P_CASH('P001',3);
PL/SQL procedure successfully completed.
SQL> select * from cash_detail;
VNO SDATE PROD SQTY
---------- ---------- ---- ----------
3 30.04.2013 P001 5
9 30.04.2013 P001 4
12 30.04.2013 P001 8
SQL> rollback;
Rollback complete.
If I call the procedure with par_rqty= 6
SQL>EXEC P_CASH('P001',6);
This should give following output
VNO SDATE PROD SQTY
---------- ---------- ---- ----------
3 30.04.2013 P001 2
9 30.04.2013 P001 4
12 30.04.2013 P001 8
Anybody please help.
|
|
|
Re: Delete records [message #583833 is a reply to message #583828] |
Sun, 05 May 2013 08:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Quote:I have changed the code ...
Well, if you call *that* code an improvement, then that's just what you deserve. I don't think I'll create the whole solution for you; someone else might.
|
|
|