i2b2/ACT script

[rosemonde2]

1. It does not identify or count categories of data that have not been incorporated into the ACT related work. This is fully expected, but limits the utility to only describe how a site SHRINE node will respond, and not what is the overall availability of data in i2b2. Some of these are really different domains of data, but some are different structure for the same category of data (ie. our Cancer Registry ontology, which mixes diagnoses, procedures, and other facts or our Microbiology is a more detailed structure for a type of "labs")

2. It assumes that the site ETL and specifically the concept paths adhere to the ACT Paths. This is expected also, given the ACT context, but there are legitimate reasons that a site would not fit this expectation. Most notably, this approach does not incorporate any expectation that a sight might be using an adapter mapping file for their ACT implementation. In our case, we have a pre-existing i2b2 infrastructure and a view-based model for handling various i2b2 and shrine instances. This allows us to avoid replication of the observation_fact table for each i2b2/SHRINE instance. Our ACT/SHRINE activity includes the use of an adapter mapping file in order to maintain our own internal structures (not ACT specific) and still be able to respond to the SHRINE instances. Thus, our internal paths differ from the paths in the scripts. There are a few other nuanced differences such as out use of multiple ontology tables rather than a single table.

3. The concept of "Notes" is just a simple fact and not knowing what exactly that means makes it pretty useless.

4. Also, There were some syntax problems that cause compiler or execution errors with this version: https://github.com/ncats/CTSA-Metrics/blob/master/Informatics%20Metric%20i2b2-ACT%20Oracle.sql

Line 166: Contains a fatal error. Oracle comments begin with --, not //
// Save the summary level of the counts -- assumes there are no categorical overlaps in dimcode defn!
Once you fix that comment, the procedure compiles without error.

But when you try to run it, line 135 contains a run-time error ‘missing right parentheses’. The set of parenthese after sysdate() have to be removed. Sysdate() is a MySQL function, Sysdate is the Oracle version
Bad version in their script is:
select count(distinct patient_num) into total_dob from PATIENT_DIMENSION where birth_date is not null and extract(year from birth_date)>1900 and extract(year from birth_date)<extract(year from sysdate());
The correct version is:
select count(distinct patient_num) into total_dob from PATIENT_DIMENSION where birth_date is not null and extract(year from birth_date)>1900 and extract(year from birth_date)<extract(year from sysdate);

[KenGersing]

I have take Jim's comments and incorporated the change tot he the Comment syntax and Sysdate. This has not been tested