Written by

Question Ewan Whyte · Feb 21, 2018

HL7 segment query using SQL

#Ensemble #HL7 #Message Search #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

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

Add reply

Comments

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.

0 0

Dinesh Ensemble Jul 29, 2020 to Jeff Morgan

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.

0 0