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:
| Table of Contents |
|---|
...
[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.
...
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 can be any valid path, or *** which means the entire message
SQL Functions
Left(expression,match/count) - Returns the left most character from a string
parameter: expression - must evaluate to a string
parameter: match - characters to match,
or parameter count - number of characters
Example: Left('1234.56','.')='1234'
Example: Left('1234.56', 4)=='1234'
Right(expression,match/count) - Returns the right most characters from a string
parameter: expression - must evaluate to a string
parameter: match - characters to match
or parameter count - number of characters
Example: Right('1234.56','.')='56'
Example: Right('1234.56', 4)='34.56'
Trim(expression,string) - Trims characters from the start and end of the specified expression
parameter: expression - must evaluate to a string
parameter: string - characters to be trimmed
Example: Trim('001234.5600', 0)='1234.56'
LTrim(expression, string) - Trims characters from the left of the specified expression
parameter: expression - must evaluate to a string
parameter: string - characters to be trimmed
Example: Trim('001234.5600', 0)='1234.5600'
RTrim(expression, string) - Trims characters from the right of the specified expression
parameter: expression - must evaluate to a string
parameter: string - characters to be trimmed
Example: Trim('001234.5600', 0)='1234.5600'
ToUpper(expression) - Turns the expression to an uppercase value
parameter: expression - must evaluate to a string
Example: ToUppper('abc')='ABC'
ToLower(expression) - Turns the expression to an lowercase value
parameter: expression - must evaluate to a string
Example: ToLower('ABC')='abc'
Len(expression) - Returns the length of the specified expression
parameter: expression - must evaluate to a string
Example: Len('123456')=6
SubString(expression,offset,length) - Returns a portion of the given string using zero-based positioning
parameter: expression - must evaluate to a string
parameter: offset - offset into the string (first char is offset 1)
parameter: length - maximum characters to return
Example: SubString('001234.5600',2,4)='1234'
Query Examples
No where clause. Useful for exporting data
...