• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 893
  • Last Modified:

MERGE statement question in PL/SQL

For additional perspective on what's happening http://www.experts-exchange.com/Database/Oracle/Q_27842830.html

First time user of the MERGE statement and I am not sure how to use it for my current situation.

 MERGE INTO ACCESS_LOG
                            USING (SELECT TRUNC(TIMESTAMP) ACCESS_DATE
                                         ,EXTRACT(HOUR FROM CAST(A.TIMESTAMP AS TIMESTAMP)) ACCESS_HOUR
                                         ,COUNT(*)
                                         ,SYSTIMESTAMP
                                   FROM SYS.DBA_AUDIT_TRAIL@dblink A
                                   WHERE A.ACTION_NAME = 'LOGON'
                                         AND COALESCE(L_MAX_AUDIT_TIME
                                                     ,MIN_LOG_DATE) < CAST(A.TIMESTAMP AS TIMESTAMP)
                                   GROUP BY TRUNC(A.TIMESTAMP)
                                           ,EXTRACT(HOUR FROM CAST(A.TIMESTAMP AS TIMESTAMP)));

Open in new window


I am trying to use a merge in place of the below, my former statement:

INSERT INTO ACCESS_LOG
                                          (SELECT TRUNC(TIMESTAMP) ACCESS_DATE
                                                 ,EXTRACT(HOUR FROM CAST(A.TIMESTAMP AS TIMESTAMP)) ACCESS_HOUR
                                                 ,COUNT(*)
                                                 ,SYSTIMESTAMP
                                           FROM SYS.DBA_AUDIT_TRAIL@Dblink A
                                           WHERE A.ACTION_NAME = 'LOGON'
                                                 AND COALESCE(L_MAX_AUDIT_TIME,MIN_LOG_DATE) < CAST(A.TIMESTAMP AS TIMESTAMP)
                                           GROUP BY TRUNC(A.TIMESTAMP)
                                                   ,EXTRACT(HOUR FROM CAST(A.TIMESTAMP AS TIMESTAMP)));

Open in new window


The reason I need a merge is because when I run a package that inserts fresh rows into my table, I am getting duplicate rows. Can someone help me edit this to become a functioing merge? Thanks
0
Mark_Co
Asked:
Mark_Co
  • 4
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
I don't think a merge is what you want.  A merge will either perform an insert for 'new' rows or 'update' existing rows based on some key fields.

Why would you want to update existing audit records?

As for the duplicates, you need to debug the insert/select to see why there are duplicates and not try to create a kludge work-around as a 'fix'.
0
 
sdstuberCommented:
you're missing the ON clause and the WHEN MATCHED clause and the WHEN NOT MATCHED clause

ON - this is like a join condition that specifies which rows in USING will correspond to which rows in your merge table

WHEN MATCHED - this is the update when a matching row is found

WHEN NOT MATCHED - this is the insert when a matching row is not found
0
 
Mark_CoAuthor Commented:
I do want to update them actually. I need to overwrite the previous data
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Mark_CoAuthor Commented:
incomplete but working on it...

 ) audit_trail_tbl
                                                   ON (log_tbl.audit_timestamp = a.timestamp)
                                                   WHEN MATCHED THEN UPDATE SET log_tbl.login_count = log_tbl.login_count + 
                                                   ;

Open in new window

0
 
sdstuberCommented:
please post more complete code,  
your snippet refers to columns and table aliases that don't exist

I'm happy to try to help you fix any syntax errors but you need to supply more

based on what you do have though,  I think your ON clause needs to be on both the day and the hour
0
 
Mark_CoAuthor Commented:
sorry, here it is

 MERGE INTO ACCESS_LOG LOG_TBL
                            USING (SELECT TRUNC(TIMESTAMP) ACCESS_DATE
                                         ,EXTRACT(HOUR FROM CAST(A.TIMESTAMP AS TIMESTAMP)) ACCESS_HOUR
                                         ,COUNT(*)
                                         ,SYSTIMESTAMP
                                   FROM SYS.DBA_AUDIT_TRAIL@dblink A
                                   WHERE A.ACTION_NAME = 'LOGON'
                                         AND COALESCE(L_MAX_AUDIT_TIME
                                                     ,MIN_LOG_DATE) < CAST(A.TIMESTAMP AS TIMESTAMP)
                                   GROUP BY TRUNC(A.TIMESTAMP)
                                           ,EXTRACT(HOUR FROM CAST(A.TIMESTAMP AS TIMESTAMP))) AUDIT_TRAIL_TBL
                            ON (LOG_TBL.AUDIT_TIMESTAMP = A.TIMESTAMP) 
                            WHEN MATCHED THEN
                                          UPDATE
                                          SET LOG_TBL.LOGIN_COUNT = LOG_TBL.LOGIN_COUNT +
                            WHEN NOT MATCHED THEN
                                          INSERT
                                          
                                          ;

Open in new window

0
 
sdstuberCommented:
change "something1234" to the real names in your access_log


MERGE INTO access_log log_tbl
     USING (SELECT   TRUNC(timestamp) access_date,
                     EXTRACT(HOUR FROM CAST(a.timestamp AS TIMESTAMP)) access_hour,
                     COUNT(*) cnt,
                     SYSTIMESTAMP extraction_time
                FROM sys.dba_audit_trail@dblink a
               WHERE     a.action_name = 'LOGON'
                     AND COALESCE(l_max_audit_time, min_log_date) < CAST(a.timestamp AS TIMESTAMP)
            GROUP BY TRUNC(a.timestamp), EXTRACT(HOUR FROM CAST(a.timestamp AS TIMESTAMP))) audit_trail_tbl
        ON (    log_tbl.something1 = audit_trail_tbl.access_date
            AND long_tbl.something2 = audit_trail_tbl.access_hour)
WHEN MATCHED
THEN
    UPDATE SET log_tbl.login_count  = log_tbl.login_count + audit_trail_tbl.cnt
WHEN NOT MATCHED
THEN
    INSERT     (something1, something2, something3, something4)
        VALUES (
                   audit_trail_tbl.access_date,
                   audit_trail_tbl.access_hour,
                   audit_trail_tbl.cnt,
                   audit_trail_tbl,
                   extraction_time);
0
 
Mark_CoAuthor Commented:
Thank you!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now