This discussion is archived
0 Replies Latest reply: Sep 22, 2010 4:47 PM by 799680 RSS

Generating A CRF Completion Report

799680 Newbie
Currently Being Moderated
Hi,

I am in the process of creating a CRF completion report, by site, patient and visit. The problem I have with my current SQL is that it doesn't pull in records for DCIS I am expectiing but there is no data yet. Here is my SQL:

I thought the outer join statment 'a.dci_id = c.dci_id(+)' would give this to me but it isn't :( Any help would be appreciate!

SELECT c.domain, a.site_id, trim(a.patient), trim(c.SHORT_NAME) AS dci_name, trim(a.visit_number), trim(a.clin_plan_eve_name) AS visit_name,
trim(a.subevent_number), trim(a.data_lock_flag), trim(a.received_dcm_status_code) as status, trim(a.document_number), trim(a.blank_flag), rxc.rdc.rdci_verify_status ('P', a.received_dci_id) AS verify_status,
rxc.rdc.rdci_app_status ('P', a.received_dci_id) as approve_status, (b.n) AS number_of_discrepancies
FROM rxc.received_dcms a,
(SELECT discrepancy_rev_status_code, received_dcm_id, COUNT (*) AS n
FROM rxc.discrepancy_entries a
WHERE discrepancy_rev_status_code LIKE '%REV%'
GROUP BY discrepancy_rev_status_code, received_dcm_id) b,
rxc.dcis c
WHERE received_dcm_status_code != 'REMOVED'
AND a.received_dcm_id = b.received_dcm_id(+)
AND a.dci_id = c.dci_id(+)
ORDER BY patient, visit_number, subevent_number

I modified the above statement from a post in OHSUG.

and

SELECT "OCL_STUDY_SITES"."STUDY_SITE", "OCL_INVESTIGATORS"."LAST_NAME", "OCL_STUDIES"."TITLE", "OCL_STUDY_SITES"."SITE_ID", "OCL_STUDIES"."STUDY"
FROM ("RXA_DES"."OCL_STUDY_SITES" "OCL_STUDY_SITES" CROSS JOIN "RXA_DES"."OCL_STUDIES" "OCL_STUDIES") INNER JOIN ("RXA_DES"."OCL_INVESTIGATORS" "OCL_INVESTIGATORS" INNER JOIN "RXA_DES"."OCL_STUDY_SITE_ROLES" "OCL_STUDY_SITE_ROLES" ON "OCL_INVESTIGATORS"."INVESTIGATOR_ID"="OCL_STUDY_SITE_ROLES"."INVESTIGATOR_ID") ON "OCL_STUDY_SITES"."SITE_ID"="OCL_STUDY_SITE_ROLES"."SITE_ID"
ORDER BY "OCL_STUDY_SITES"."STUDY_SITE"

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points