Replies:

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

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. 

After conferring with the WRC, it seems there really wasn't a "problem" at all, but rather that while they share the same syntax for INSERT commands,  there is a syntax difference in how HealthShare and Microsoft each handle an UPDATE command.

The following code works in HealthShare/ObjectScript:

Set tSQL = "UPDATE table_name (col1, col2, col3) VALUES ('val1', 'val2', 'val3') WHERE accession_number='"_accessionNumber_"'"
Set tSC = ..Adapter.ExecuteUpdateParmArray(.nrows,tSQL,.par)

And this is how Microsoft wants it:

Set tSQL = "UPDATE IRIS_Study SET col1='val1', col2='val2', col3='val3' WHERE accession_number='"_accessionNumber_"'"
Set tSC = ..Adapter.ExecuteUpdateParmArray(.nrows,tSQL,.par)

And it can still be parameterized by using:

SET col1=?, col2=?, col3=?
Followers:
Following:
Jonathan has not followed anybody yet.
Global Masters badges:
Jonathan has no Global Masters badges yet.