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 |
|
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 |
|
Michel Cadot
Messages: 68665 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 #689195 is a reply to message #689192] |
Thu, 05 October 2023 15:11 |
|
Barbara Boehmer
Messages: 9097 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.
|
|
|
Goto Forum:
Current Time: Sun Jun 30 01:03:49 CDT 2024
|