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:
...
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 TAB_NAME [APPEND]] [where < search_condition >]
...
No where clause. Useful for exporting data
select MSH-7 as 'Date/Time', MSH-9, PID-3, PID-5, PV1-2
Select male patients into a tab named Males
select* INTO Males where PID-8='M'
Select male patients appended to an existing (or new) tab named Males
select * INTO Males APPEND where PID-8='M'
Select messages with matching text any where in the message.
select * where *** CONTAINS 'SACRAMENTO' OR *** CONTAINS 'SAN JOSE'
Select messages where any OBX segment contains 'FOO BAR'.
select * where OBX[*] CONTAINS 'FOO BAR'
Select messages where any OBX-5, or OBX-5 repeat, contains 'FOO BAR'.
select * where OBX[*]-5[*] CONTAINS 'FOO BAR'
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');
...