How to get Segment values in SQL query editor

Question

Question

#ObjectScript #SQL #Caché #Ensemble #HealthShare #InterSystems IRIS #InterSystems IRIS for Health

Hi,

I am trying to find out individual segment values in Message viewer and it display the values in Management portal, copied the query and run it on SQL editor, Segment values displayed with blank. Is there a way to get the individual segment values using query?

Product version: IRIS 2020.1

Discussion (4)2

Log in or sign up to continue

Jeffrey Drumm · Sep 15

I"m assuming you're talking about HL7 here ...

InterSystems does a bit of magic in the background to display the Message Body fields in the message viewer, and it's not immediately available to you. However, I've written a custom SQL function that fetches the field at the supplied path (you'll need supply the path as its defined by the Schema/DocType of the message).

Example:

SELECT TOP 100 head.ID As ID,
        {fn RIGHT(%EXTERNAL(head.TimeCreated),999 )} As TimeCreated,
        head.SessionId As Session,
        head.Status As Status,
        head.SourceConfigName As Source,
        head.TargetConfigName As Target,
        HICG.GetMsg(head.MessageBodyId,'MSH:9') As MessageType
    FROM
        %IGNOREINDEX Ens.MessageHeader.SessionId Ens.MessageHeader head
    WHERE
        head.SessionId = head.%ID
        AND head.MessageBodyClassname = 'EnsLib.HL7.Message'
        AND head.SourceConfigName = 'SourceBusinessHostName'
    ORDER BY
        head.ID Desc