Question
Jonathan Anglin · Jan 6

Searching messages and using EnsLib_HL7.Message.RawContent in DTL

Problem to Solve: When an HL7 message hits a particular Process, I need to use the placer order number therein to query the messages that came in over the last 24 hours on a particular Service and grab discrete pieces of data that came in the latest message that has that placer order number.

The code I'm (currently) using looks like this:

<assign property='FullMessage'     action='set' value='"PID||TEST"' />
<sql>
    <![CDATA[SELECT Full_Message INTO :FullMessage, head.ID As ID, {fn RIGHT(%EXTERNAL(head.TimeCreated),999 )} As TimeCreated,
head.SessionId As Session,
head.Status As Status,
CASE head.IsError WHEN 1 THEN 'Error' ELSE 'OK' END As Error,
head.SourceConfigName As Source,
head.TargetConfigName As Target,
head.TargetConfigName,
head.MessageBodyClassName As BodyClassname,
(SELECT LIST(PropValue) FROM EnsLib_HL7.SearchTable WHERE head.MessageBodyId = DocId And PropId=7) As SchTbl_ORC2,
EnsLib_HL7.SearchTable.PropId As SchTbl_PropId,
head.MessageBodyId As BodyId,
EnsLib_HL7.Message.%ID As Body_ID,
EnsLib_HL7.Message.RawContent AS Full_Message
FROM Ens.MessageHeader head, EnsLib_HL7.SearchTable, EnsLib_HL7.Message
WHERE Head.TimeCreated >= DATEADD(day, -1, GETDATE())
AND Head.TimeCreated <= GETDATE()
AND ((head.SourceConfigName = 'SITE ORM-ORU Inbound')
AND (head.TargetConfigName = 'SITE ORM 1 PreProcessor')
AND head.MessageBodyClassName=(('EnsLib.HL7.Message'))
AND head.MessageBodyId = EnsLib_HL7.SearchTable.DocId
AND EnsLib_HL7.SearchTable.PropId = 6
AND EnsLib_HL7.SearchTable.PropValue = '1234567890'
AND head.MessageBodyClassName='EnsLib.HL7.Message'
AND head.MessageBodyId=EnsLib_HL7.Message.%ID)
ORDER BY head.ID Desc
    ]]></sql>

<assign property='target.{PID:2}'   action='set' value='..Piece(..Piece(FullMessage,"PID",2),"|",3)' />

The Issues:

  • The variable FullMessage does not populated, I just get "TEST" in target.{PID:2}.
  • My query does not grab the latest message. I tried using MAX and GREATEST on the head.ID and TimeCreated, but it didn't work.
  • I need to swap out the hard-coded search table value for a :placerOrderNumber variable, but one problem at a time.
  • If I remove "Full_Message INTO :FullMessage" piece, the query runs fine in SQL (aside from getting multiple results)

Any help from the team would be greatly appreciated, and if I'm going about this all wrong please do let me know.

Product version: IRIS 2020.1
0
0 100
Discussion (4)2
Log in or sign up to continue

What happens if you use SELECT TOP 1 Full_Message ...? Embedded SQL doesn't work for queries that return multiple rows* (assuming that's what you mean by "multiple results").

* unless you're using cursor-based queries

Hi Jeff

TOP...of course! Yeah that only returns one result when I run the query in the SQL window. However, I still do not get the FullMessage variable populated from the query when run in the DTL, so the "SELECT TOP 1 Full_Message INTO :FullMessage" bit is not working. 

Have you tried doing this instead?

SELECT EnsLib_HL7.Message.RawContent INTO :FullMessage, head.ID As ID, {fn RIGHT(%EXTERNAL(head.TimeCreated),999 )} As TimeCreated, head.SessionId As Session, head.Status As Status,
...

Also, I've gotta make a plug for keeping in mind the disclaimers on the use of RawContent:
Note that this is a truncated version suitable for use in SQL results and visual inspection, but not a complete or definitive representation of the document.  

After working with Joao Navarro in the WRC, here's what I discovered.

My initial query was based on the results of the "Show Query" button in the Management Portal Message Viewer. While that provided a good start, the SELECT clause that Navarro suggested returned what I was looking for.

A couple of notes:

  1. Dates are stored in UTC, so use "GETUTCDATE" when trying to limit the search parameters by time.
  2. RawContent contains carriage returns between the segments. You may need to remove $CHAR(13) with the appropriate function.

SELECT EnsLib_HL7.Message.RawContent AS Full_Message INTO :FullMessage
FROM Ens.MessageHeader head, EnsLib_HL7.SearchTable, EnsLib_HL7.Message
WHERE head.TimeCreated >= DATEADD(hour, -24, GETUTCDATE())
AND ((head.SourceConfigName = 'SITE ORM-ORU Inbound')
AND (head.TargetConfigName = 'SITE ORM 1 PreProcessor')
AND head.MessageBodyClassName=(('EnsLib.HL7.Message'))
AND head.MessageBodyId = EnsLib_HL7.SearchTable.DocId
AND EnsLib_HL7.SearchTable.PropId = 6
AND EnsLib_HL7.SearchTable.PropValue = :placerOrderNum
AND head.MessageBodyClassName='EnsLib.HL7.Message'
AND head.MessageBodyId=EnsLib_HL7.Message.%ID)
ORDER BY head.ID Desc