Paul Riker · Jul 18, 2016

HL7 Segment Query

I want to query the cache database for messages where a specific HL7 segment equals a specific value. Does Cache have a pipe to XML or hl7 segment query function?

0 1,586
Discussion (3)1
Log in or sign up to continue

In Ensemble or HealthShare there is a class for the HL7 segments but it is not a persistent class so it is not exposed to SQL. The segments are stored in raw globals for speed and the virtual document paths are  parsed at run time for efficiency. 

You can use the message browser to look for messages where a particular segment/field value has a given value. But if you look at the generated SQL it doesn't seem to make sense. That is because the SQL query is used in a cursor and the access to segment values is done in COS inside the cursor iteration.

If you can be more specific about what you are trying to do i might be able to give you some pointers about how to get where you want to go.

To do this in the message viewer, add an extended criteria to your search parameters.  Criterion Type = VDoc Property Path.  Class = EnsLib.HL7.Message.  DocType = Schema:Name of the type of documents you're searching for (e.g. 2.3.1:ADT_A01).  Property Path = the VDoc path of the field you're searching on (e.g. MSH:SendingApplication)

It should be noted that the message viewer will use SQL to get a list of messages based on the 'Basic Criteria' and then it loops over the results applying each of the 'Extended Criteria' as a filter.  This can be very slow.  If it takes more than 2 minutes, the message viewer will time out and either the Basic Criteria needs to be refined in order to return fewer results, or the search for messages must be done with code.

To do this with code, use embedded SQL to search on message headers based on basic criteria and then loop over the results checking VDoc paths.  Here's an example which looks for messages from a particular day that have a particular value in MSH:3, the Sending Application field:

&SQL(DECLARE C1 CURSOR FOR SELECT MessageBodyId INTO :id FROM Ens.MessageHeader WHERE MessageBodyClassName='EnsLib.HL7.Message' AND TimeCreated BETWEEN '2016-07-15' AND '2016-07-16')
while (SQLCODE = 0) {
   set msg = ##class(EnsLib.HL7.Message).%OpenId(id)
   if (msg.GetValueAt("MSH:SendingApplication") = "Ensemble") {
      //do something with this message

If you plan on doing frequent searches against a specific HL7 segment/field, you can speed up the searches significantly by using Ensemble's "search tables" mechanism for adding an index for that field.

Fields indexed in the search table are listed in the dropdown box on the message viewer search, or can be queried using SQL by joining EnsLib_HL7.SearchTable:

select msg.ID,msg.TimeCreated,
from EnsLib_HL7.SearchTable srchTbl
join enslib_hl7.message msg on 
join Ens.MessageHeader msgHdr on msgHdr.MessageBodyId=srchTbl.docid
where  (srchTbl.propid=(select propid from Ens_Config.SearchTableProp where classextent='EnsLib.HL7.SearchTable' and name='PatientSSN')
       and srchTbl.propvalue = '123-25-4612')
       and msgHdr.SourceConfigName='HL7.File.In'