Home » Developer & Programmer » Forms » Delete Records [merged 3] (Oracel Forms 6.)
Delete Records [merged 3] [message #583447] Wed, 01 May 2013 02:10 Go to next message
mdamjad
Messages: 45
Registered: May 2005
Member
./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 195 times)
Re: Delete records [message #583450 is a reply to message #583447] Wed, 01 May 2013 02:40 Go to previous messageGo to next message
cookiemonster
Messages: 11076
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.
Re: Delete records [message #583573 is a reply to message #583450] Wed, 01 May 2013 21:46 Go to previous messageGo to next message
mdamjad
Messages: 45
Registered: May 2005
Member
It is a single table cash_detail and want the logic to be applied on WHEN_BUTTON_PRESSED .
Delete Records [message #583583 is a reply to message #583447] Thu, 02 May 2013 00:32 Go to previous messageGo to next message
mdamjad
Messages: 45
Registered: May 2005
Member
./fa/10778/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 .

Table name : cash_detail , logic should be processed at WHEN_BUTTON_PRESSED
Please help with code .
  • Attachment: cash.JPG
    (Size: 13.49KB, Downloaded 168 times)
Re: Delete records [message #583594 is a reply to message #583573] Thu, 02 May 2013 02:32 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
So what's the problem with writing a delete statement?
Delete Records [message #583614 is a reply to message #583447] Thu, 02 May 2013 05:51 Go to previous messageGo to next message
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 #583616 is a reply to message #583614] Thu, 02 May 2013 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is clear as mud.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Delete Records [message #583617 is a reply to message #583616] Thu, 02 May 2013 06:20 Go to previous messageGo to next message
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 #583618 is a reply to message #583617] Thu, 02 May 2013 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why didn't you post as requested?

Regards
Michel
Re: Delete Records [message #583619 is a reply to message #583618] Thu, 02 May 2013 06:38 Go to previous messageGo to next message
mdamjad
Messages: 45
Registered: May 2005
Member
Oracle version 8.1.7

I am giving the return_qty from Oracle Forms 4.5
Re: Delete Records [message #583620 is a reply to message #583619] Thu, 02 May 2013 06:44 Go to previous messageGo to next message
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 #583623 is a reply to message #583620] Thu, 02 May 2013 07:15 Go to previous messageGo to next message
mdamjad
Messages: 45
Registered: May 2005
Member
Sorry , please help me .

Regards
Amjad
Re: Delete Records [message #583625 is a reply to message #583620] Thu, 02 May 2013 07:52 Go to previous messageGo to next message
joy_division
Messages: 4531
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 messageGo to next message
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 messageGo to next message
Roachcoach
Messages: 1221
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 #583643 is a reply to message #583630] Thu, 02 May 2013 11:58 Go to previous messageGo to next message
Littlefoot
Messages: 19697
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's one option; see how it works and, possibly, improve it.
A procedure:
create or replace procedure p_cash (par_code in varchar2,
                                    par_rqty  in number)
is
  l_sum number := 0;
begin
  for cur_r in (select vno, sqty
                from cash_detail
                where prod_code = par_code
                order by vno)
  loop
    l_sum := l_sum + cur_r.sqty;
    if l_sum < par_rqty then
       -- sum of SQTY is smaller than RETURN QTY - do nothing
	   null;	
	elsif l_sum = par_rqty then
	   -- sum of SQTY is equal to RETURN QTY - just delete records
	   -- (i.e. no update necessary)
	   delete from cash_detail
	     where vno <= cur_r.vno;
    elsif l_sum > par_rqty then
       -- sum of SQTY is higher than RETURN QTY: delete records up to
       -- the current one; update current record	   
       delete from cash_detail
	     where vno < cur_r.vno;
		 
       update cash_detail set
         sqty = l_sum - par_rqty
         where vno = cur_r.vno;
    end if;
  end loop;
end p_cash;

Testing:
SQL> exec p_cash('P001', 17);

PL/SQL procedure successfully completed.

SQL> select * from cash_detail;

       VNO SDATE      PROD       SQTY
---------- ---------- ---- ----------
        12 30.04.2013 P001          3

SQL> rollback;

Rollback complete.
SQL> exec p_cash('P001', 12);

PL/SQL procedure successfully completed.

SQL> select * from cash_detail;

       VNO SDATE      PROD       SQTY
---------- ---------- ---- ----------
        12 30.04.2013 P001          8

SQL> rollback;

Rollback complete.
SQL> exec p_cash('P001', 100);

PL/SQL procedure successfully completed.

SQL> select * from cash_detail;

       VNO SDATE      PROD       SQTY
---------- ---------- ---- ----------
         1 30.04.2013 P001          1
         2 30.04.2013 P001          2
         3 30.04.2013 P001          5
         9 30.04.2013 P001          4
        12 30.04.2013 P001          8

SQL> rollback;

Rollback complete.

SQL> 
Re: Delete records [message #583688 is a reply to message #583643] Fri, 03 May 2013 00:31 Go to previous messageGo to next message
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 #583726 is a reply to message #583643] Fri, 03 May 2013 06:58 Go to previous messageGo to next message
Littlefoot
Messages: 19697
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Littlefoot
Here's one option; see how it works and, possibly, improve it.


So, what did you do to improve it?
Re: Delete records [message #583828 is a reply to message #583726] Sun, 05 May 2013 06:38 Go to previous messageGo to next message
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
Littlefoot
Messages: 19697
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.
Previous Topic: Oracle Forms & Reports
Next Topic: Single Record Works. All Not Workss.
Goto Forum:
  


Current Time: Fri Oct 24 23:18:07 CDT 2014

Total time taken to generate the page: 0.11082 seconds