How to loop through columns and retrieve column values?

Question

I have a question regarding Oracle cursor.

I need to compare the values in a selected list of columns, see which records have changed after update. The table is huge, the only way to distinguish between new and old record is by the Batch_ID.

The output table will include these information:

enter image description here

So i have something very basic like below, except that i need to write like hundreds of SELECT statement in order to go through hundreds of columns.

SELECT a.*, b.LAST_name AS last_name_updated --- here the variable could be ethnicity, income category etc..
from (SELECT person_id, last_name
      FROM   person  
      WHERE batch_id = (select max(batch_id) from person)
     ) a FULL OUTER JOIN
     (SELECT person_id, last_name
      FROM   person  
      WHERE batch_id = (select max(batch_id) - 1 from person)
     ) b
     ON a.person_id = b.person_id
WHERE  nvl(a.last_name,0) <> nvl(b.last_nm,0)

I need to go through a list of about 500 field name like Income, Race, Last_Name etc and capture all the changes, if any, in all these columns.

how do i go about this without writing hundreds of these SELECT statements?

Thanks in advance for your help!

here is the base SQL query. SELECT a.*, b.LAST_name AS last_name_updated from (SELECT person_id, last_name FROM person WHERE batch_id = (select max(batch_id) from person) ) a FULL OUTER JOIN (SELECT person_id, last_name FROM person WHERE batch_id = (select max(batch_id) - 1 from person) ) b ON a.person_id = b.person_id WHERE nvl(a.last_name,0) <> nvl(b.last_nm,0) — LycorisRadiata
– LycorisRadiata, Commented Jul 7, 2017 at 22:01
I only work with SQL server, but in Oracle there must be a system table that stores all of this. Look in the master database. You can then query that to get any info about any object in your database. — Michael Z.
– Michael Z., Commented Jul 7, 2017 at 22:03
Edit your question and provide sample data and desired results. A cursor is probably not needed for this purpose. — Gordon Linoff
– Gordon Linoff, Commented Jul 7, 2017 at 22:04
@GordonLinoff you're like a StackOverflow god! Good job on all those points. You must have helped many many people. — Michael Z.
– Michael Z., Commented Jul 7, 2017 at 22:05
Thanks @MichaelZ... I'm trying to capture all the value change in each column of interest and build a table to store all the before and after value. — LycorisRadiata
– LycorisRadiata, Commented Jul 7, 2017 at 22:21

Brian Leach · Accepted Answer · 2017-07-08 00:24:23Z

1

This is a generic SQL statement that will use ALL_TAB_COLS to generate a SQL statement that can be used to compare columns:

  SELECT    CASE WHEN column_id = '1' THEN q'[ select 'batchid: ' || batchid ]' END
         || CASE
                WHEN column_name <> 'BATCHID'
                THEN
                       q'[ || case when ]'
                    || column_name
                    || q'[<> lag(]'
                    || column_name
                    || q'[) over (partition by batchid order by col1, col2) then ' ]'
                    || column_name
                    || ' has changed'' end'
            END
         || CASE WHEN column_id = MAX (column_id) OVER () THEN q'[ as changemessage from testtable]' END
             sql
    FROM all_tab_cols a
   WHERE table_name = 'TESTTABLE'
ORDER BY column_id;

Test Case:

CREATE TABLE testtable
(
    batchid   INTEGER
  , col1      INTEGER
  , col2      INTEGER
);

BEGIN
    FOR batchid IN 1 .. 3
    LOOP
        FOR col1 IN 1 .. 3
        LOOP
            FOR col2 IN 1 .. 2
            LOOP
                INSERT INTO testtable
                     VALUES (batchid, col1, col2);
            END LOOP;
        END LOOP;
    END LOOP;
END;

Execute the SQL and you generate the following SQL statement:

select 'batchid: ' || batchid 
     || case when COL1<> lag(COL1) over (partition by batchid order by col1, col2) then ' COL1 has changed' end
     || case when COL2<> lag(COL2) over (partition by batchid order by col1, col2) then ' COL2 has changed' end as changemessage from testtable

I will leave it to you to reduce the result set:

CHANGEMESSAGE
batchid: 1
batchid: 1
batchid: 1
batchid: 1
batchid: 1 COL2 has changed
batchid: 1
batchid: 1
batchid: 1
batchid: 1 COL1 has changed COL2 has changed
batchid: 1
batchid: 1
batchid: 1
batchid: 1 COL2 has changed
batchid: 1
batchid: 1
batchid: 1
batchid: 1 COL1 has changed COL2 has changed
batchid: 1
batchid: 1
batchid: 1
batchid: 1 COL2 has changed
batchid: 1
batchid: 1
batchid: 1
batchid: 2
batchid: 2
batchid: 2
batchid: 2
batchid: 2 COL2 has changed
batchid: 2
batchid: 2
batchid: 2
batchid: 2 COL1 has changed COL2 has changed
batchid: 2
batchid: 2
batchid: 2
batchid: 2 COL2 has changed
batchid: 2
batchid: 2
batchid: 2
batchid: 2 COL1 has changed COL2 has changed
batchid: 2
batchid: 2
batchid: 2
batchid: 2 COL2 has changed
batchid: 2
batchid: 2
batchid: 2
batchid: 3
batchid: 3
batchid: 3
batchid: 3
batchid: 3 COL2 has changed
batchid: 3
batchid: 3
batchid: 3
batchid: 3 COL1 has changed COL2 has changed
batchid: 3
batchid: 3
batchid: 3
batchid: 3 COL2 has changed
batchid: 3
batchid: 3
batchid: 3
batchid: 3 COL1 has changed COL2 has changed
batchid: 3
batchid: 3
batchid: 3
batchid: 3 COL2 has changed
batchid: 3
batchid: 3
batchid: 3

answered Jul 8, 2017 at 0:24

Brian Leach

2,1111 gold badge14 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

LycorisRadiata Over a year ago

Wow thanks Brian! The code is great example! Thanks much!

LycorisRadiata Over a year ago

Hi @Brian Leach, thanks again! What if I want to display the before and after update value, between the latest and the prior batch? I want the comparison of different values to be stored in an audit trail table, with 4 main columns: batch_id, field_name, current_value, previous_value. In the Field_Name, i will compare about 600 columns such as income, height, address_state, etc... how to make sure that the difference of these information is captured? Thanks again!

Brian Leach Over a year ago

@LycorisRadiata You can adjust the generated select statement to perform an insert. I would not store the before/after values, but rather the primary key that would point to the appropriate records. A view could then display before/after values however you want.

LycorisRadiata Over a year ago

Thanks @Brian Leach... yeah, the business requirement is to store the before and after values in an audit table with primary key and distinguishable person_id etc.. The business ask is to have all the changes made to the columns stored in a table...

Collectives™ on Stack Overflow

How to loop through columns and retrieve column values?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related