OraFAQ Forum: SQL & PL/SQL » Need a pl/sql block to be entered in existing package

Home » SQL & PL/SQL » SQL & PL/SQL » Need a pl/sql block to be entered in existing package ("Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.18.0.0.0")

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

Need a pl/sql block to be entered in existing package [message #689192]

Thu, 05 October 2023 10:31

indupriyav_2023
Messages: 3
Registered: January 2023

Junior Member

1)
Sample table has 2 groups belonging to 3 different from_date.

Here only if all group belonging to same from_date is 0, it should be removed

Eg:
Mytable

No Group Fromdate todate amount
50126 MAT 01-Oct-23 21-Oct-23 20300
50126 MAT 22-Oct-23 18-Nov-23 121
50126 MAT 19-Nov-23 11-May-24 0
50126 RUGUNDERLAY 22-Oct-23 18-Nov-23 0
50126 RUGUNDERLAY 19-Nov-23 11-May-24 0
50126 RUGUNDERLAY 01-Oct-23 21-Oct-23 17500

Only Records with group as MAT and group with RUGUNDERPLAY and fromdate 19-Nov-23 should be removed since all group belonging to same from_date is 0
Records with group as MAT and fromdate 22-Oct-23 should NOT be removed since all group belonging to same from_date is not 0

Eg:
No Group Fromdate todate amount
50126 MAT 01-Oct-23 21-Oct-23 20300
50126 MAT 22-Oct-23 18-Nov-23 0
50126 MAT 19-Nov-23 11-May-24 0
50126 RUGUNDERLAY 22-Oct-23 18-Nov-23 0
50126 RUGUNDERLAY 19-Nov-23 11-May-24 0
50126 RUGUNDERLAY 01-Oct-23 21-Oct-23 17500

Records with group as MAT and group with RUGUNDERPLAY and fromdate 19-Nov-23 should be removed
Records with group as MAT and group with RUGUNDERPLAY and fromdate 22-Nov-23 should be removed

2) Another record set

There may be extra recrod with different from_date for both groups.

Mytable

No Group Fromdate todate amount
50126 MAT 01-Oct-23 21-Oct-23 20300
50126 MAT 22-Oct-23 18-Nov-23 121
50126 MAT 19-Nov-23 11-May-24 0
50126 MAT 12-May-24 18-May-24 0
50126 RUGUNDERLAY 22-Oct-23 18-Nov-23 0
50126 RUGUNDERLAY 19-Nov-23 11-May-24 0
50126 RUGUNDERLAY 01-Oct-23 21-Oct-23 17500
50126 RUGUNDERLAY 12-May-24 18-May-24 17500
50126 SOCCER 22-Oct-23 18-Nov-23 0
50126 SOCCER 19-Nov-23 11-May-24 0
50126 SOCCER 01-Oct-23 21-Oct-23 17500
50126 SOCCER 12-May-24 18-May-24 17500

========================================================

Additional comments :

Sample table has 2 groups belonging to 3 different from_date.

Here only if all group belonging to same from_date is 0, it should be removed

Case 1)

Eg:
Mytable

No Group Fromdate todate amount
50126 MAT 01-Oct-23 21-Oct-23 20300
50126 MAT 22-Oct-23 18-Nov-23 121
50126 MAT 19-Nov-23 11-May-24 0
50126 RUGUNDERLAY 22-Oct-23 18-Nov-23 0
50126 RUGUNDERLAY 19-Nov-23 11-May-24 0
50126 RUGUNDERLAY 01-Oct-23 21-Oct-23 17500

Only Records with group as MAT and group with RUGUNDERPLAY and fromdate 19-Nov-23 should be removed since all group belonging to same from_date is 0
Records with group as MAT and fromdate 22-Oct-23 should NOT be removed since all group belonging to same from_date is not 0

After PL/SQL fix given the result should be :

Mytable

No Group Fromdate todate amount
50126 MAT 01-Oct-23 21-Oct-23 20300
50126 MAT 22-Oct-23 18-Nov-23 121
50126 RUGUNDERLAY 22-Oct-23 18-Nov-23 0
50126 RUGUNDERLAY 01-Oct-23 21-Oct-23 17500

2) Case 2:

Eg:

No Group Fromdate todate amount
50126 MAT 01-Oct-23 21-Oct-23 20300
50126 MAT 22-Oct-23 18-Nov-23 0
50126 MAT 19-Nov-23 11-May-24 0
50126 RUGUNDERLAY 22-Oct-23 18-Nov-23 0
50126 RUGUNDERLAY 19-Nov-23 11-May-24 0
50126 RUGUNDERLAY 01-Oct-23 21-Oct-23 17500

Records with group as MAT and group with RUGUNDERPLAY and fromdate 19-Nov-23 should be removed
Records with group as MAT and group with RUGUNDERPLAY and fromdate 22-Oct-23 should be removed

After PL/SQL fix given the result should be :

No Group Fromdate todate amount
50126 MAT 01-Oct-23 21-Oct-23 20300
50126 RUGUNDERLAY 01-Oct-23 21-Oct-23 17500

insert into mytable values('50126','MAT','01-Oct-23','21-Oct-23',20300)
insert into mytable values('50126','MAT','22-Oct-23','18-Nov-23',0)
insert into mytable values('50126','MAT','19-Nov-23','11-May-24',0)
insert into mytable values('50126','RUGUNDERLAY','22-Oct-23','18-Nov-23',0)
insert into mytable values('50126','RUGUNDERLAY','19-Nov-23',11-May-24',0)
insert into mytable values('50126','RUGUNDERLAY','01-Oct-23',21-Oct-23',17500)

Report message to a moderator

Re: Need a pl/sql block to be entered in existing package [message #689193 is a reply to message #689192]

Thu, 05 October 2023 10:38

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

Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Wed, 11 January 2023 11:09

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

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.
...

In addition, if you don't feedback in your topics you won't have more help.

Note that '01-Oct-23' is a STRING and not a DATE, proof:

SQL> create table t (dt date);

Table created.

SQL> insert into t values ('01-Oct-23');
insert into t values ('01-Oct-23')
                      *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL> select to_date('01-Oct-23') from dual;
select to_date('01-Oct-23') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Report message to a moderator

Re: Need a pl/sql block to be entered in existing package [message #689194 is a reply to message #689192]

Thu, 05 October 2023 10:44

indupriyav_2023
Messages: 3
Registered: January 2023

Junior Member

Here only if all group belonging to same from_date has amount value as 0, that recrod should be removed

Report message to a moderator

Re: Need a pl/sql block to be entered in existing package [message #689195 is a reply to message #689192]

Thu, 05 October 2023 15:11

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

Please note how the following uses code tags that maintain indentation and align columns, making it easier to read. Also, please read all of the comments prefaced by hyphens.

-- create table statement that you should have provided:

SCOTT@orcl_12.1.0.2.0> create table mytable
  2    (no	  number,
  3  	groups	  varchar2(11),
  4  	fromdate  date,
  5  	todate	  date,
  6  	amount	  number);

Table created.

-- corrections to the only insert statements that you provided
-- using to_date to convert character data to dates
-- with months as numbers instead of names that can be different in different languages
-- and specifying 4-digit years instead of 2-digit which may default to the wrong century
-- and adding a semicolon after each insert statement:

SCOTT@orcl_12.1.0.2.0> insert into mytable values
  2    ('50126','MAT',to_date('01-10-2023','dd-mm-yyyy'),to_date('21-10-2023','dd-mm-yyyy'),20300);

1 row created.

SCOTT@orcl_12.1.0.2.0> insert into mytable values
  2    ('50126','MAT',to_date('22-10-2023','dd-mm-yyyy'),to_date('18-11-2023','dd-mm-yyyy'),0);

1 row created.

SCOTT@orcl_12.1.0.2.0> insert into mytable values
  2    ('50126','MAT',to_date('19-11-2023','dd-mm-yyyy'),to_date('11-05-2024','dd-mm-yyyy'),0);

1 row created.

SCOTT@orcl_12.1.0.2.0> insert into mytable values
  2    ('50126','RUGUNDERLAY',to_date('22-10-2023','dd-mm-yyyy'),to_date('18-11-2023','dd-mm-yyyy'),0);

1 row created.

SCOTT@orcl_12.1.0.2.0> insert into mytable values
  2    ('50126','RUGUNDERLAY',to_date('19-11-2023','dd-mm-yyyy'),to_date('11-05-2024','dd-mm-yyyy'),0);

1 row created.

SCOTT@orcl_12.1.0.2.0> insert into mytable values
  2    ('50126','RUGUNDERLAY',to_date('01-10-2023','dd-mm-yyyy'),to_date('21-10-2023','dd-mm-yyyy'),17500);

1 row created.

SCOTT@orcl_12.1.0.2.0> commit;

Commit complete.

-- starting data:

SCOTT@orcl_12.1.0.2.0> select * from mytable
  2  /

        NO GROUPS      FROMDATE        TODATE              AMOUNT
---------- ----------- --------------- --------------- ----------
     50126 MAT         Sun 01-Oct-2023 Sat 21-Oct-2023      20300
     50126 MAT         Sun 22-Oct-2023 Sat 18-Nov-2023          0
     50126 MAT         Sun 19-Nov-2023 Sat 11-May-2024          0
     50126 RUGUNDERLAY Sun 22-Oct-2023 Sat 18-Nov-2023          0
     50126 RUGUNDERLAY Sun 19-Nov-2023 Sat 11-May-2024          0
     50126 RUGUNDERLAY Sun 01-Oct-2023 Sat 21-Oct-2023      17500

6 rows selected.

-- package and procedure with pl/sql block containing delete statement:

-- package specification:
SCOTT@orcl_12.1.0.2.0> create or replace package mypackage
  2  as
  3    procedure myprocedure;
  4  end mypackage;
  5  /

Package created.

SCOTT@orcl_12.1.0.2.0> show errors
No errors.


-- package body:
SCOTT@orcl_12.1.0.2.0> create or replace package body mypackage
  2  as
  3    procedure myprocedure
  4    is
  5    begin
  6  	 -- added pl/sql block:
  7  	 begin
  8  	   -- delete statement
  9  	   delete from mytable t1
 10  	   where
 11  		  not exists -- could use instead:  t1.fromdate not in
 12  		    (select t2.fromdate
 13  		     from   mytable t2
 14  		     where  t2.fromdate = t1.fromdate
 15  		     and    t2.no = t1.no -- remove this line if no does not matter
 16  		     and
 17  			    nvl(t2.amount,0) -- change to t2.amount if null not considered 0
 18  			    != 0);
 19  	 end;
 20    end myprocedure;
 21  end mypackage;
 22  /

Package body created.

SCOTT@orcl_12.1.0.2.0> show errors
No errors.

-- execution:

SCOTT@orcl_12.1.0.2.0> execute mypackage.myprocedure

PL/SQL procedure successfully completed.

-- results:

SCOTT@orcl_12.1.0.2.0> select * from mytable
  2  /

        NO GROUPS      FROMDATE        TODATE              AMOUNT
---------- ----------- --------------- --------------- ----------
     50126 MAT         Sun 01-Oct-2023 Sat 21-Oct-2023      20300
     50126 RUGUNDERLAY Sun 01-Oct-2023 Sat 21-Oct-2023      17500

2 rows selected.

Report message to a moderator

Previous Topic:	Improve select with inner JOIN
Next Topic:	Unable to compile procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 14:27:16 CDT 2024