OraFAQ Forum: Forms » Delete Records [merged 3]

Home » Developer & Programmer » Forms » Delete Records [merged 3] (Oracel Forms 6.)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Delete Records [merged 3] [message #583447]

Wed, 01 May 2013 02:10

mdamjad
Messages: 45
Registered: May 2005

Member

/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 1374 times)

Report message to a moderator

Re: Delete records [message #583450 is a reply to message #583447]

Wed, 01 May 2013 02:40

cookiemonster
Messages: 13925
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.

Report message to a moderator

Re: Delete records [message #583573 is a reply to message #583450]

Wed, 01 May 2013 21:46

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 .

Report message to a moderator

Delete Records [message #583583 is a reply to message #583447]

Thu, 02 May 2013 00:32

mdamjad
Messages: 45
Registered: May 2005

Member

/forum/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 1324 times)

Report message to a moderator

Re: Delete records [message #583594 is a reply to message #583573]

Thu, 02 May 2013 02:32

cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester

Senior Member

So what's the problem with writing a delete statement?

Report message to a moderator

Delete Records [message #583614 is a reply to message #583447]

Thu, 02 May 2013 05:51

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 .

Report message to a moderator

Re: Delete Records [message #583616 is a reply to message #583614]

Thu, 02 May 2013 06:00

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: Delete Records [message #583617 is a reply to message #583616]

Thu, 02 May 2013 06:20

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 .

Report message to a moderator

Re: Delete Records [message #583618 is a reply to message #583617]

Thu, 02 May 2013 06:30

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Why didn't you post as requested?

Regards
Michel

Report message to a moderator

Re: Delete Records [message #583619 is a reply to message #583618]

Thu, 02 May 2013 06:38

mdamjad
Messages: 45
Registered: May 2005

Member

Oracle version 8.1.7

I am giving the return_qty from Oracle Forms 4.5

Report message to a moderator

Re: Delete Records [message #583620 is a reply to message #583619]

Thu, 02 May 2013 06:44

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

Report message to a moderator

Re: Delete Records [message #583623 is a reply to message #583620]

Thu, 02 May 2013 07:15

mdamjad
Messages: 45
Registered: May 2005

Member

Sorry , please help me .

Regards
Amjad

Report message to a moderator

Re: Delete Records [message #583625 is a reply to message #583620]

Thu, 02 May 2013 07:52

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.

Report message to a moderator

Re: Delete records [message #583626 is a reply to message #583594]

Thu, 02 May 2013 08:09

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 .

Report message to a moderator

Re: Delete records [message #583630 is a reply to message #583626]

Thu, 02 May 2013 08:41

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.

Report message to a moderator

Re: Delete records [message #583643 is a reply to message #583630]

Thu, 02 May 2013 11:58

Littlefoot
Messages: 21813
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>

Report message to a moderator

Re: Delete records [message #583688 is a reply to message #583643]

Fri, 03 May 2013 00:31

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

Report message to a moderator

Re: Delete records [message #583726 is a reply to message #583643]

Fri, 03 May 2013 06:58

Littlefoot
Messages: 21813
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?

Report message to a moderator

Re: Delete records [message #583828 is a reply to message #583726]

Sun, 05 May 2013 06:38

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.

Report message to a moderator

Re: Delete records [message #583833 is a reply to message #583828]

Sun, 05 May 2013 08:26

Littlefoot
Messages: 21813
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.

Report message to a moderator

Previous Topic:	Oracle Forms & Reports
Next Topic:	Single Record Works. All Not Workss.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 03 11:06:37 CDT 2024