Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate Insertion while executing Procedure (Oracle 12c)
Duplicate Insertion while executing Procedure [message #675239] |
Mon, 18 March 2019 05:34 |
|
rajmat483
Messages: 3 Registered: March 2019
|
Junior Member |
|
|
Hi,
When I execute the below query, I get the expected result. However, when I schedule a job to populate a table using the same query, the table gets populated with the same records twice, thrice etc. as many times as the job runs.
The query is:
SELECT
to_timestamp((TO_CHAR(SYSDATE,'DD-MON-RR')|| ' '|| TO_CHAR(m.contact_date,'HH:MI:SS AM')), 'DD-MON-RR HH:MI:SS AM') AS contact_date,
m.interactivechannelname,
m.interactionpointname,
nvl(m.channel_type,'UNKNOWN')channel_type,
m.cbu,
m.segment,
m.visits todays_visits,
nvl(y.visits,0) yesterdays_visits,
m.visits - nvl(y.visits,0) today_vs_yesterday_visits_diff,
nvl(w.visits,0) last_6_days_visits,
m.visits - nvl(w.visits,0) today_vs_6days_visits_diff,
m.accepts todays_accepts,
nvl(y.accepts,0) yesterdays_accepts,
m.accepts - nvl(y.accepts,0) today_vs_yesterday_accepts,
nvl(w.accepts,0) last_6_days_accepts,
m.accepts - nvl(w.accepts,0) today_vs_6days_accepts,
CASE
WHEN ( m.visits - y.visits ) / 100 > c.prev_day_visit_threshold OR ( y.visits - m.visits ) / 100 > c.prev_day_visit_threshold THEN 'YES'
WHEN ( m.visits - w.visits ) / 100 > c.last_6_days_visit_threshold OR ( w.visits - m.visits ) / 100 > c.last_6_days_visit_threshold THEN 'YES'
WHEN ( m.accepts - y.accepts ) / 100 > c.prev_day_accept_threshold OR ( y.accepts - m.accepts ) / 100 > c.prev_day_accept_threshold THEN 'YES'
WHEN ( m.accepts - w.accepts ) / 100 > c.last_6_days_accept_threshold OR ( w.accepts - m.accepts ) / 100 > c.last_6_days_accept_threshold THEN 'YES'
END
AS alert_msg,
CASE
WHEN ( m.visits - y.visits ) / 100 > c.prev_day_visit_threshold OR ( y.visits - m.visits ) / 100 > c.prev_day_visit_threshold THEN c.alarm_text
WHEN ( m.visits - w.visits ) / 100 > c.last_6_days_visit_threshold OR ( w.visits - m.visits ) / 100 > c.last_6_days_visit_threshold THEN c.alarm_text
WHEN ( m.accepts - y.accepts ) / 100 > c.prev_day_accept_threshold OR ( y.accepts - m.accepts ) / 100 > c.prev_day_accept_threshold THEN c.alarm_text
WHEN ( m.accepts - w.accepts ) / 100 > c.last_6_days_accept_threshold OR ( w.accepts - m.accepts ) / 100 > c.last_6_days_accept_threshold THEN c.alarm_text
END
AS alarm_text
FROM
mccm_ops_monitoring_1_days m
LEFT JOIN mccm_ops_monitoring_pre_days y ON TO_CHAR(m.contact_date,'HH24:MI:SS AM') = TO_CHAR(y.contact_date,'HH24:MI:SS AM')
AND m.interactivechannelname = y.interactivechannelname
AND m.interactionpointname = y.interactionpointname
AND nvl(m.channel_type,'UNKNOWN') = nvl(y.channel_type,'UNKNOWN')
AND m.cbu = y.cbu
AND m.segment = y.segment
LEFT JOIN mccm_ops_monitoring_6_days w ON TO_CHAR(m.contact_date,'HH24:MI:SS AM') = TO_CHAR(w.contact_date,'HH24:MI:SS AM')
AND m.interactivechannelname = w.interactivechannelname
AND m.interactionpointname = w.interactionpointname
AND nvl(m.channel_type,'UNKNOWN') = nvl(w.channel_type,'UNKNOWN')
AND m.cbu = w.cbu
AND m.segment = w.segment
LEFT JOIN alarm_control_table c ON m.interactivechannelname = c.interactivechannelname
AND m.interactionpointname = c.interactionpointname
AND nvl(m.channel_type,'UNKNOWN') = nvl(c.channel_type,'UNKNOWN')
AND m.cbu = c.cbu
AND m.segment = c.segment
ORDER BY
1 ;
And the Procedure is:
CREATE OR REPLACE PROCEDURE mccm_alarm_notification
AS
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE KPI.ALARM_NOTIFICATION';
INSERT /*+APPEND+*/ INTO alarm_notification
SELECT
to_timestamp( (TO_CHAR(SYSDATE,'DD-MON-RR')
|| ' '
|| TO_CHAR(m.contact_date,'HH:MI:SS AM') ),'DD-MON-RR HH:MI:SS AM') AS contact_date,
m.interactivechannelname,
m.interactionpointname,
nvl(m.channel_type,'UNKNOWN') channel_type,
m.cbu,
m.segment,
m.visits todays_visits,
nvl(y.visits,0) yesterdays_visits,
m.visits - nvl(y.visits,0) today_vs_yesterday_visits_diff,
nvl(w.visits,0) last_6_days_visits,
m.visits - nvl(w.visits,0) today_vs_6days_visits_diff,
m.accepts todays_accepts,
nvl(y.accepts,0) yesterdays_accepts,
m.accepts - nvl(y.accepts,0) today_vs_yesterday_accepts,
nvl(w.accepts,0) last_6_days_accepts,
m.accepts - nvl(w.accepts,0) today_vs_6days_accepts,
CASE
WHEN ( m.visits - y.visits ) / 100 > c.prev_day_visit_threshold
OR ( y.visits - m.visits ) / 100 > c.prev_day_visit_threshold THEN 'YES'
WHEN ( m.visits - w.visits ) / 100 > c.last_6_days_visit_threshold
OR ( w.visits - m.visits ) / 100 > c.last_6_days_visit_threshold THEN 'YES'
WHEN ( m.accepts - y.accepts ) / 100 > c.prev_day_accept_threshold
OR ( y.accepts - m.accepts ) / 100 > c.prev_day_accept_threshold THEN 'YES'
WHEN ( m.accepts - w.accepts ) / 100 > c.last_6_days_accept_threshold
OR ( w.accepts - m.accepts ) / 100 > c.last_6_days_accept_threshold THEN 'YES'
END
AS alert_msg,
CASE
WHEN ( m.visits - y.visits ) / 100 > c.prev_day_visit_threshold
OR ( y.visits - m.visits ) / 100 > c.prev_day_visit_threshold THEN c.alarm_text
WHEN ( m.visits - w.visits ) / 100 > c.last_6_days_visit_threshold
OR ( w.visits - m.visits ) / 100 > c.last_6_days_visit_threshold THEN c.alarm_text
WHEN ( m.accepts - y.accepts ) / 100 > c.prev_day_accept_threshold
OR ( y.accepts - m.accepts ) / 100 > c.prev_day_accept_threshold THEN c.alarm_text
WHEN ( m.accepts - w.accepts ) / 100 > c.last_6_days_accept_threshold
OR ( w.accepts - m.accepts ) / 100 > c.last_6_days_accept_threshold THEN c.alarm_text
END
AS alarm_text
FROM
mccm_ops_monitoring_1_days m
LEFT JOIN mccm_ops_monitoring_pre_days y ON TO_CHAR(m.contact_date,'HH24:MI:SS AM') = TO_CHAR(y.contact_date,'HH24:MI:SS AM')
AND m.interactivechannelname = y.interactivechannelname
AND m.interactionpointname = y.interactionpointname
AND nvl(m.channel_type,'UNKNOWN') = nvl(y.channel_type,'UNKNOWN')
AND m.cbu = y.cbu
AND m.segment = y.segment
LEFT JOIN mccm_ops_monitoring_6_days w ON TO_CHAR(m.contact_date,'HH24:MI:SS AM') = TO_CHAR(w.contact_date,'HH24:MI:SS AM')
AND m.interactivechannelname = w.interactivechannelname
AND m.interactionpointname = w.interactionpointname
AND nvl(m.channel_type,'UNKNOWN') = nvl(w.channel_type,'UNKNOWN')
AND m.cbu = w.cbu
AND m.segment = w.segment
LEFT JOIN alarm_control_table c ON m.interactivechannelname = c.interactivechannelname
AND m.interactionpointname = c.interactionpointname
AND nvl(m.channel_type,'UNKNOWN') = nvl(c.channel_type,'UNKNOWN')
AND m.cbu = c.cbu
AND m.segment = c.segment;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered refreshing stage table- '
|| sqlcode
|| ' -ERROR- '
|| sqlerrm);
END;
Please advise and help on avoiding this duplication.
Let me know if any other details are required.
Thanks!
|
|
|
|
|
|
Re: Duplicate Insertion while executing Procedure [message #675245 is a reply to message #675244] |
Mon, 18 March 2019 06:48 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You're going to have to explain in more detail what is going on.
At the end of the day you've got a truncate followed by an insert/select.
If you're ending up with different numbers of rows each time you run it then either:
a) the select returns different numbers of rows each time
b) some other code is also modifying the table.
If it's a then that'll have to be because the data in the tables being queried has changed between procedure calls.
We know nothing about your tables and data.
We know nothing about what that query is actually returning
If you want us to help you work out what the problem is then you're going to have to explain those things to us.
Post a Test case - create table and insert statements so we can recreate your tables and data, along with expected output.
|
|
|
Re: Duplicate Insertion while executing Procedure [message #675398 is a reply to message #675245] |
Wed, 27 March 2019 07:19 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
What schema are you running your procedure. You are attempting to truncate a table in the "KPI" schema, however truncate is a DDL statement but something like "delete from KPI.ALARM_NOTIFICATION" is a DML and is allowed if an insert privilege was granted on the table to your schema.
|
|
|
Goto Forum:
Current Time: Mon Jun 17 01:37:08 CDT 2024
|