The lab transform does not account for local children. The correct ont_parent could be 1 or 2 levels up from the node. I think the easiest way to deal with this is probably to add a join on loinc code:
(I tested this briefly on Partners data, but not on our test data)
select distinct ont_parent.c_basecode, lab.c_basecode
FROM i2b2fact M
inner join pmnENCOUNTER enc on enc.patid = m.patient_num and enc.encounterid = m.encounter_Num -- Constraint to selected encounters
inner join pcornet_lab lab on lab.c_basecode = M.concept_cd and lab.c_fullname like '\PCORI\LAB_RESULT_CM\%'
inner join pcornet_lab ont_loinc on lab.pcori_basecode=ont_loinc.pcori_basecode and ont_loinc.c_basecode like 'LOINC:%'
inner JOIN pcornet_lab ont_parent on ont_loinc.c_path=ont_parent.c_fullname
inner join pmn_labnormal norm on ont_parent.c_basecode=norm.LAB_NAME
The ont_loinc line is new and the ont_parent line changed.
The lab transform does not account for local children. The correct ont_parent could be 1 or 2 levels up from the node. I think the easiest way to deal with this is probably to add a join on loinc code: (I tested this briefly on Partners data, but not on our test data)
The ont_loinc line is new and the ont_parent line changed.