Report Writing

Interoperability and HIE

Privacy and Security

SQL Tip: Parsing Packed Fields in MEDITECH 6.x

Written by Thomas Harlan, Data Repository Technical Team Lead for Reporting Services, Iatric Systems

RWS-Blog-HeaderImage-report-writing-services-2018-02.jpg

It's common in MEDITECH’s 6.x Data Repository (DR) to encounter fields in PCM or EMR where a single long text field contains a packed data structure. For example, when a multi-select item has been stored with a variety of delimiters and special formatting. If you haven’t run across these fields before, here’s an example from EMR where we’re looking at the Discharge Problem field, which is a standard field attached to a documentation section for Clinical Impression:

RWS-Blog-Parsing-Packed-Fields-Code-1.gif

As you can see, we get a variety of data items:

  • The first row has a single problem, and an Intelligent Medical Object (IMO) number has been picked from a list
  • The second row has a completely free-text entered problem without an IMO number
  • The third row has two problems, with their respective IMO numbers, stored in this one single field

We could just display the entire field – strange formatting and all – but that won't help our end-user, who just wants to see a readable problem list on the report. Something like:

RWS-Blog-Parsing-Packed-Fields-Code-2.gifWhat we must do, then, is split each field into a table-structure where there is one row per problem. We can see from the data that a “}|{“ delimits each problem entry. Inside each entry, we want to check for the sixth item (delimited by |) for a description (rows 1 and 3), and if there’s nothing there, then we use the first item (which in this data is a free-text string beginning with a period), instead.

Using record three as an example, we’ll go from one row delimited by }|{ into two sections:

RWS-Blog-Parsing-Packed-Fields-Code-3.gifTo two rows…

RWS-Blog-Parsing-Packed-Fields-Code-4.gifAnd then break out the sixth element of each row:

RWS-Blog-Parsing-Packed-Fields-Code-5.gifTo do that, we first split the packed field, using IatricSplit() [code!] to produce a little table of rows, and then we use IatricPiece() [code!] to pick out element six from the string column in each row:

RWS-Blog-Parsing-Packed-Fields-Code-6.gifBut… when we get at the data like this, we will often get this error:

RWS-Blog-Parsing-Packed-Fields-Code-7.gifThat is because the IatricSplit() function uses T-SQL XML XPATH processing to do the split operation. And this error tells us there are characters in the field that XPATH can’t process because they are reserved in XML. So, what to do? 

In our DR Resource Center, we use a CTE/tally-table approach to avoid using XML XPATH to do the split. But we can also “escape” the reserved characters into their XML equivalents (also called an entity reference):

RWS-Blog-Parsing-Packed-Fields-Table-1.gif

That % one is a Microsoft-specific extension, and not part of the XML standard. However, since you’re using SQL Server to do this processing, you must account for it. To make this easier, we created a little IatricEscXML() function to do the swapping out… [code!]

Then we can mix that in and get…

RWS-Blog-Parsing-Packed-Fields-Code-8.gifIf you’ve been up on MEDITECH 6.x for some time, when you run this code against your real data, you will likely see that the older data doesn’t follow the structure for this field we’ve shown above (with six elements in each problem string), but the more recent data does:

RWS-Blog-Parsing-Packed-Fields-Code-9.gif

We just need a little cleanup to remove the leading and trailing characters, so we add a pair of REPLACE(s) to get rid of that.

Maybe you notice that in addition to picking an IMO Problem from a list, the person doing the discharge documentation can also enter complete free-text, which is prefixed with a period:

RWS-Blog-Parsing-Packed-Fields-Code-10.gifNow we don’t have an element six at all! Instead, we want the value in element one! (Oh, why do you make us cry so?)

To address that, we add a CASE statement as well, to see if element six is empty, and if it is, then get element one instead. And finally, we use XML PATH(‘’) to create a list of problems (if there are more than one), separated by ; characters for displaying on a report.

RWS-Blog-Parsing-Packed-Fields-Code-11.gif

Now we have something we can reasonably display to the end user.

Extra Credit!

Create a table-valued function based on this example, allowing you to JOIN to this data structure without the performance impact of making it a VIEW.

If you need more help…

Our Report Writing team can help you fix reports, create new ones, make old ones faster, and much more. Simply reach out to your Iatric Systems Account Executive or Karen Roemer (karen.roemer@iatric.com) to discuss how we can help support your team!

RW-HELP-Button-Blue.png

Topics: Report Writing Services, Report Writing Tips and Tricks, Data Repository, SQL

Subscribe to the Report Writing blog.

Receive the latest articles directly in your inbox.
Enter your email address and click SUBSCRIBE:

Comments

0 COMMENTS