CODEWITHSUNDEEP
oracle11g
Nandini
Nandini

Reputation: 27

Change Data Capture in oracle 11g

I'm working on Change Data Capture in oracle 11g. I want only the updated columns in the change table for example

EMPNO   ENAME   JOB     MGR     HIREDATE     DEPTNO

5   ford    clerk   104 01-JAN-12   11 
5   ford    clerk   104 01-JAN-12   18

consider the above table as change table, here only the department number has been updated so I need only the deptno column in the table. How to do this?

Upvotes: 1

Views: 177

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521569

One option which might fit your needs and which would not require dynamic SQL would be to aggregate employee records and then check if a change has occurred in each column.

SELECT
    EMPNO,
    ENAME,
    CASE WHEN COUNT(DISTINCT JOB) > 1
         THEN 'yes' ELSE 'no' END AS job_changed,
    CASE WHEN COUNT(DISTINCT MGR) > 1
         THEN 'yes' ELSE 'no' END AS mgr_changed,
    CASE WHEN COUNT(DISTINCT HIREDATE) > 1
         THEN 'yes' ELSE 'no' END AS hire_date_changed,
    CASE WHEN COUNT(DISTINCT DEPTNO) > 1
         THEN 'yes' ELSE 'no' END AS dept_changed
FROM yourTable
GROUP BY
    EMPNO,
    ENAME

Upvotes: 1

Related Questions