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")
Need a pl/sql block to be entered in existing package [message #689192] Thu, 05 October 2023 10:31 Go to next message
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)
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68597
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
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 Go to previous messageGo to next message
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
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 Go to previous message
Barbara Boehmer
Messages: 9077
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.
Previous Topic: Improve select with inner JOIN
Next Topic: Unable to compile procedure
Goto Forum:
  


Current Time: Sat Feb 24 02:21:25 CST 2024