HL7 SQL
- Jonathan Reis
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[SEG_NUM]-F[REPEAT_NUM][[.C].S]
where:
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:
MSH|^~\&|SPC|M||M|20040503223716||ORM^O01|176201653|P|2.2| |
PID|1||0000307656^^^M&FEE&&FIE&&FOO&&FUM^MR~0000858462^^^P&FOO&BAR^MR |
OBR|1||3844834|2035^NM HEPATOBILIARY DUCT^MRD|||200405030939|| |
OBX|1|ST|&GDT|1|TEXT1~TEXT2|| |
OBX|2|ST|&GDT|1|TEXT3~TEXT4|| |
OBX|3|ST|&GDT|1|TEXT5~TEXT6|| |
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 |
OBX[*]-5[*] CONTAINS 'TEXT' |
Messages where the OBR-4 contains exactly 2035^NM HEPATOBILIARY DUCT^MRD |
OBR-4 = '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.
select * where *** CONTAINS 'SACRAMENTO' OR *** CONTAINS 'SAN JOSE'
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');