SQL Oracle – SUBQUERY to selectively pull data from *different* column, depending on table conditions

Jumping straight in. We have tables (TABLE) and audit tables (AUDITTABLE) that store changes to the main table.

In the table, we have entries like:

Changing the LocationID of row #1 from 35 to 50, and PersonID from 1 to 3 would be recorded in the audit table like (omitting some columns for brevity, there are also items like created and createdby to track when and by whom a value was changed):

I am writing a query where I am pulling data from the audit table:

SELECT
    sourceid AS ID,
    id AS EVENT_ID
    key AS COLUMN,
    oldvalue AS PREVIOUS_ENTRY
FROM
    AUDITTABLE

I want a sub-query within the select that pulls the currently stored value within the main table, depending upon the KEY from the audit table (which matches the main table column names one-for-one) for the current row. I can do this with a Case statement, but I have about 100 tables to do this for with each one having unique column names (and multiple dozens of columns each), which would be a nightmare to write.

EDIT

I’m looking at the recommended articles after writing this, and it seems like everything is pointing to writing case statements. If that’s ultimately the answer, please just let me know this isn’t doable in the manner I need, and no need to waste your time writing example case statements or unioned queries–that’s well within my skillset, despite the physical agony that will ensue

I was thinking something like the below (which obviously doesn’t work, but shows the general idea of what I’m trying to achieve).

SELECT
    sourceid AS ID,
    id AS EVENT_ID
    key AS COLUMN,
    oldvalue AS PREVIOUS_ENTRY,
    (SELECT TABLE. || (SELECT TEMPAUDITTABLE.KEY 
                       FROM AUDITTABLE AS TEMPAUDITTABLE 
                       WHERE TEMPAUDITTABLE.SOURCEID = AUDITTABLE.SOURCEID) 
     FROM Table 
     WHERE Table.ID = Audittable.sourceid) AS Current_Value
FROM
    AUDITTABLE

I know you can get table column names from USER_TAB_COLUMNS which may be part of the solution, but I have no idea how to implement something like that.

I apologize in advance if I’m missing something obvious. I’m fairly decent at writing queries, but I’m about 90% self-taught and there are likely some gaps in my knowledge.

To anyone willing to help point me in the right direction, thank you!

I’ve tried google, copilot, tech-on-the-net, and searching through stack overflow questions. I’ve also referenced the documentation for SQL Oracle.

>Solution :

To make it generic you’ll need to use dynamic SQL. The simplest way is a simple function that returns a value for a given table, row and column.

create or replace function current_value(in_table_name in varchar2,
                                         in_row_column in varchar2, 
                                         in_row_id in integer,
                                         in_select_column in varchar2)
  return varchar2
as
  var_result varchar2(4000);
begin
  execute immediate 'select max("'||in_select_column||'") 
                       from "'||in_table_name||'" 
                      where "'||in_row_column||'" = :id' 
               into var_result 
           using in in_row_id;

  return var_result;
end;

This will of course cast any column to a string, so if you need a date or number either reconvert it on output or create other functions or even packaged overloads to handle other datatypes. If all the columns you are tracking are numeric, then swap out the varchar2 for a number, otherwise using varchar2 is safest since most datatypes can be cast to it. It also clearly wouldn’t handle a LOB and expects the row identifier to be numeric. Whatever complexity you need would need to be built-in, but this gives you the basic framework.

Also, to have any hope of decent performance, you’d want to ensure that the row identifying column (ID) is indexed in each table. If it’s the PK it already should be. Then you simply query the audit table and call the function for each row:

SELECT
    sourceid AS ID,
    id AS EVENT_ID
    key AS COLUMN,
    oldvalue AS PREVIOUS_ENTRY,
    current_value('MYTABLE','ID',id,key) current_value
FROM AUDITTABLE