This feature allows you to use SQL language to aggregate data. One example is SELECT * WHERE *** CONTAINS 'SACRAMENTO' OR *** CONTAINS 'SAN JOSE,' which returned the following:

HL7 Path Syntax

An HL7 Path is defined as:



SEG – a 3 character segment name. For example PID.

[SEG_NUM] – SEG_NUM is an integer value where SEG_NUM>0 or SEG_NUM='*'. If [SEG_NUM] is omitted, SEG[1] is assumed. If [SEG_NUM]='*', all matching segments are included in the search. For example OBR[2] means the second OBR segment, whereas OBR[1] and OBR are equivalent.

F – is an integer value where F>0. For example PID-3, means the 3rd field in the PID segment.

[REPEAT_NUM]– is the Field Repeat number where REPEAT_NUM>0 or REPEAT_NUM='*'. If REPEAT_NUM='*' all repeating fields are included in the query. If [REPEAT_NUM] is omitted, F[1] is assumed.

C – C>0 or is absent. If C is absent, all components in the field are included separated by '^'. For example, PID-3 means PID-3.1 +PID-3.2+PID-3.3...PID-3.N.

S – S>0 or is absent. If S is absent, all subcomponents in the component are included separated by '&'. If S is omitted, S=1 is assumed. For example, PID-3.2.3 means the third subcomponent of the component of PID-3, and PID-3.2 means PID-3.2.1+PID-3.2.2+PID-3.2.3...PID-3.2.N.

Example HL7 Paths

For a stream of messages with a format similar to the following:



OBR|1||3844834|2035^NM HEPATOBILIARY DUCT^MRD|||200405030939||




Messages where the first patient identifier is 0000307656

PID-3.1 = '0000307656'

Messages where the 2nd patient identifier is 0000307656

PID-3[2].1 = '0000307656'

Messages where any patient identifier is 00003076566

PID-3[*].1 = '0000307656'

Messages where any Namespace ID (PID-4.2) is FEE

PID-3[*].4.2 = 'FEE'

Messages where any OBX-5, or repeat there of, has the word TEXT


Messages where the OBR-4 contains exactly 2035^NM HEPATOBILIARY DUCT^MRD


Query Syntax

SELECT [ TOP ( integer ) ] <HL7Path_list> [INTO TABLE_NAME] [where < search_condition >]

<HL7Path_list> ::= HL7Path [ [ AS ] column_alias ][,...n]

<search_condition > ::=

{ [ NOT ]  <search_condition>  }

[ { AND | OR } <search_condition>  ]

[ ,...n ]

<search_condition> ::=

{ HL7Path { = | < > | ! = | > | > = | < | < = } string_expression

| HL7Path [ NOT ] LIKE string_expression

| HL7Path [NOT] CONTAINS string_expression

| HL7Path [NOT] REGEX string_expression

| HL7Path IS [ NOT ] NULL

| HL7Path [ NOT ] IN ( string_list )


HL7Path can be any valid path, or *** which means the entire message

Query Examples

No where clause. Useful for exporting data

select MSH-7 as 'Date/Time', MSH-9, PID-3, PID-5, PV1-2


Select messages with matching text any where in the message.



Default path List, Find all male patients

select * where PID-8='M'


Find any illegal SSNs

select MSH-7, MSH-9, PID-3.2, PID-5, PID-19 as SSN

where PID-19!='' AND PID-19 NOT REGEX '^\d{3}-\d{2}-\d{4}$'


Find all Males and put them in a separate tab

select *, PID-8 into Males where PID-8='M'


Female birthdays < 1953 and male birthday less 1945

select MSH-7, MSH-9, PID-3, PID-5, PID-7, PID-8

where(PID-8='F' AND PID-7<='1953') OR (PID-8='M' AND PID-7<='1945');

Female patients that contain the last name 'SMITH'

select MSH-7 as 'Date/Time', MSH-9, PID-3, PID-5, PID-7

where PID-8='F' and PID-5.1.1 LIKE '%SMITH%';


Present, but Invalid SSN 'SMITH'

select MSH-7, MSH-9, PID-3, PID-5, PID-19 as SSN

where PID-19!='' AND PID-19 NOT REGEX '^\d{3}-\d{2}-\d{4}$';


Find Patient by original identifier, or by their new patient identifier

select MSH-7, MSH-9, PID-3, PID-5

where PID-3 IN ('800292182','800191207');