Issues Found in InformaticsMetricACTPostgres.sql (Latest commit 9a91cbb on Apr 20, 2021)

[sguptawustl]

Please see below issues found in the InformaticsMetricACTPostgres.sql (latest commit 9a91cbb on Apr 20, 2021):

1. Postgres does not support use of DUAL like other DBMS.

   INSERT INTO ctsa_clic_metric
   SELECT
   'nlp_any' as variable_name
   , '0' as one_year
   , '0' as five_year
   FROM dual;

   Change this sql to following instead:

   INSERT INTO ctsa_clic_metric
   SELECT
   'nlp_any' as variable_name
   , '0' as one_year
   , '0' as five_year
   ;

2. Throughout the script wherever there is a LIKE operation on string values that contain \, it needs to be escaped. For example, the following query will not find any matching concept_cd: SELECT CONCEPT_CD FROM i2b2demodata.CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\ACT\Procedures\ICD9\%' However, the properly escaped string value in the query as follows will find many matching concept_cds: SELECT CONCEPT_CD FROM i2b2demodata.CONCEPT_DIMENSION WHERE CONCEPT_PATH LIKE '\\ACT\\Procedures\\ICD9\\%'

This applies to all the metric components where a 'concept_path like <some string containing a \ in the value>' like search is being performed.

3. Lastly, its not an issue but more of a suggestion, can the dates be parameterized so that an end-user only has to specify them one time at the beginning of the script instead of having to replace them in every metric component in the script (about 28x2 instances). i.e. these will need to be changed every year to a different date range.
   • OBS.START_DATE BETWEEN '2020/01/01' AND '2020/12/31'
   • OBS.START_DATE BETWEEN '2016/01/01' AND '2020/12/31'

[mim18]

1 and 2 should be fixed