Ewan Whyte · Feb 21, 2018

HL7 segment query using SQL

I'm trying to get a count of specific message type with a specific entry and thought I could build the query in Message Viewer but  this does not provide counts (as far as I am aware). So when I take the SQL from 'Show Query' it omits the segment criteria as the code shows below. 

 I have attached the criteria that has been excluded. Is this possible?

Thank you

       {fn RIGHT(%EXTERNAL(head.TimeCreated),12)} AS TimeCreated,
       head.SessionId AS SESSION,
       head.Status AS Status,
       COUNT (*) AS messages,
       CASE head.IsError
           WHEN 1 THEN 'Error'
           ELSE 'OK'
       END AS Error,
       head.SourceConfigName AS SOURCE,
       head.TargetConfigName AS Target,
       head.MessageBodyClassName AS BodyClassname,
       head.MessageBodyId AS BodyId,
       EnsLib_HL7.Message.%ID AS Body_ID,
       NULL AS Body_OBR_SpecimenSource_specimensourcenameorcode_identifier
FROM Ens.MessageHeader head,
WHERE head.TargetConfigName = 'ReviewOrders Router'
  AND head.%ID >= 189143244
  AND head.%ID <= 189589516
  AND (((head.SourceConfigName = 'Review Pathology Inbound'
    OR head.TargetConfigName = 'Review Pathology Inbound'))
  AND head.MessageBodyClassName='EnsLib.HL7.Message'
  AND head.MessageBodyId=EnsLib_HL7.Message.%ID)

0 1,151
Discussion (2)2
Log in or sign up to continue


A simple example of how to count DocType's in the EnsLib_HL7.Message table is below:

SELECT Count(*) FROM EnsLib_HL7.Message
WHERE Name = 'ADT_A12'

If you need to compare Ens.MessageHeader properties too, you can do a join on the two tables:

select Count(*) from EnsLib_HL7.Message As Body
LEFT JOIN Ens.MessageHeader As Header
ON Body.ID = Header.MessageBodyId
WHERE Header.ID > 1 AND Body.Name = 'ADT_A12'

Hope this helps.

Hi Jef,

Thanks for the information on the query. Just wanted to know if there any easy way to export the query result, say to clipboard or a notepad? Appreciate any information in this regard.