New post
Question
· Feb 21, 2018

HL7 segment query using SQL

#HL7 #Message Search #SQL #Ensemble

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

SELECT head.ID AS ID, 
       {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.SourceConfigName,
       head.TargetConfigName,
       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,
     EnsLib_HL7.Message
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)
ORDER BY head.ID DESC

Discussion (2)2
Log in or sign up to continue
Jeff Morgan · Mar 7, 2018

Ewan,

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.

3 0