Published on InterSystems Developer Community (https://community.intersystems.com)

Home > SQL list all unique GT1.3 values in suspended messages

Question
Aaron Vail · Jul 12, 2021

SQL list all unique GT1.3 values in suspended messages

I'm close with this but I'm not sure how to grab JUST the GT1.3 data.  I know I can do a substring but finding the 3rd | is a tad tricky.  I've not been this deep in SQL for 15 years.

SELECT SUBSTRING(hm.RawContent, (CHARINDEX('GT1',hm.RawContent)), 50) as NameDesc
FROM Ens.MessageHeader as em, EnsLib_HL7.Message as hm
where em.Status = 'Suspended'
and em.MessageBodyId = hm.id

I expect it should flow like this...

SELECT UNIQUE SUBSTRING(RawContent, (FIND 3RD PIPE),(FIND 4TH PIPE) as NameDesc ....

#SQL #InterSystems IRIS
Product version: IRIS 2019.1

Source URL:https://community.intersystems.com/post/sql-list-all-unique-gt13-values-suspended-messages