...
| Code Block |
|---|
|
insert into
os_search_entity_keywords(identifier, entity, entity_id, name, value, status)
select
os_search_entity_keywords_seq.nextval,
'collection_protocol_registration', r.identifier,
'medicalRecordNumber', lower(pmi.medical_record_number), 1
from
catissue_part_medical_id pmi
inner join catissue_participant p on p.identifier = pmi.participant_id
inner join catissue_coll_prot_reg r on r.participant_id = p.identifier
where
p.activity_status != 'Disabled' and
r.activity_status != 'Disabled' and
pmi.medical_record_number is not null;
|
Step 3: Verify none of the MRN keywords are pointing to incorrect records. Below query should yield 0.
| Code Block |
|---|
|
select
count(*)
from
os_search_entity_keywords s
inner join catissue_coll_prot_reg r on r.identifier = s.entity_id
inner join catissue_collection_protocol cp on cp.identifier = r.collection_protocol_id
inner join catissue_participant p on p.identifier = r.participant_id
left join catissue_part_medical_id pmi on pmi.participant_id = p.identifier
where
s.name = 'medicalRecordNumber' and
s.entity = 'collection_protocol_registration' and
pmi.medical_record_number is null
|