maybe a long shot, but at work my team pulls from really complex tables which are not built only for us. We have constantly evolving definitions of how to pull certain rows to meet certain requirements. They are medical claims so for example we have columns we filter on to define things like outpatient care, denied claims, definitions of different groups of procedure or diagnosis codes, etc. The way it works right now which just breaks my soul is that different content experts basically have these definitions defined as snippets within their own schemas, and when another analyst might need them we just reach out to them, and copy and paste that into our own queries.
Not only is this just hard to keep track of and risk losing these as people cycle in and out, but say coworker A has a definition for ear infections, updates that throughout the year, well then if I have their older version of ear infection code and don't think to check if its been changed then here we are pulling different data. Also big at risk if someone leaves the org of course.
What I would love to find is a way to have all of these definitions live and able to be dynamically pulled for other analysts to use, and would automatically update for queries that end up being run on a recurring basis.
An example idea that would work for this if I could figure out (but also just might not be possible), would be to have a shared table, where each row could be for a different filter definition, a column for the actual definition, and other columns for documentation.
For example a cell in that table could be the string "WHERE PROCEDURE_CODE = 123 AND PROVIDER_INFORMATION = 'abc' ". Then another analyst could query this table of definitions to pull that string in as part of their WHERE statement in their own query. This way the table could get updated, yet if they rerun their query it would now automatically pull the updated definition. Obviously it isn't that simple though, but I have no idea if there is a way to get something like this to functionally work, but would really love if anyone has any way to make something like this happen. We are using Toad Datapoint 6.0 so if there is some other functionality in TOAD that people know of (maybe something like snippets but I can't get those to be pulled dynamically) then of course I would be open to that. The definitions table example is just what makes sense in my mind, but anything that could achieve similar functionality would be great
I have tried defining variables as part of the WHERE in my statements, and that works for when the filter needs a dynamic component such as date or set of codes, but not for when the entire WHERE statement and the columns it uses might need to change for different definitions.
In Oracle 19c and later, SQL Macros can build a library of dynamic table functions. Putting all the functions in a single package will also reduce the object bloat.
This approach is similar to views, but SQL Macros are both more complicated and more powerful than regular views. SQL Macros can be used as glorified views (as in getEarDiagnosis below), or they can be parameterized to take the table name at run time which can simplify code if there are many similar tables (as in getEarProc below), and they can also require variables for filtering (as in getEarProcAfterDate below). And the results are still returned as regular tables, so users could add their own custom filters on top of the required filters.
create table claims(diagnosis_code varchar2(100), procedure_code varchar2(100), claim_date date);
create table claims_2018_backup(diagnosis_code varchar2(100), procedure_code varchar2(100), claim_date date);
create or replace package claimManager is
function getEarDiagnosis return clob sql_macro;
function getEarProc(p_claimTable dbms_tf.table_t) return clob sql_macro;
function getEarProcAfterDate(p_claimDate date) return clob sql_macro;
end claimManager;
/
create or replace package body claimManager is
function getEarDiagnosis return clob sql_macro is
begin
return
q'[
select * from claims where diagnosis_code = 'T16.9XXA'
]';
end;
function getEarProc(p_claimTable dbms_tf.table_t) return clob sql_macro is
begin
return
q'[
select * from p_claimTable where procedure_code = '69200'
]';
end;
function getEarProcAfterDate(p_claimDate date) return clob sql_macro is
begin
return
q'[
select * from claims where procedure_code = '69200' and claim_date > trunc(p_claimDate)
]';
end;
end claimManager;
/
select * from table(claimManager.getEarDiagnosis);
select * from table(claimManager.getEarProc(p_claimTable => claims));
select * from table(claimManager.getEarProcAfterDate(p_claimDate => sysdate));
The above code is relatively simple, but it doesn't have to be that way. Each function uses PL/SQL, so you could go crazy and build your own dynamic query string based on whatever procedural logic or configuration table you want. (But dynamic SQL can be difficult, so spend extra time ensuring such a package is well commented, formatted, and designed.)