In Version 1.1 we've introduced the SQL Schema Browser. This window allows you to visually create some reasonably complex SQL Queries against the schema tables and also allows you to view the SQL that your query produced. You can run your queries and browse through the resulting messages, run message data reports and even view the contents of individual segment data tables.
Visually build a Query
The Main Query Builder
|
Uncheck any box to exclude that section from your query. |
|
Create an extended query on almost any field in any segment of the HL7 message (see Extended Data Queries). |
|
When you've built the query you like, click the 'Execute the Query' button to run it. |
|
Click the 'Show SQL' button to see the SQL statement which will be generated by your query. Great for learning how to navigate the schema to retrieve specific messages. |
|
Every time the Query Window loads, it will automatically reload the last query you've executed. In addition, you can also save your queries to disk by clicking the Save Query button. |
|
You can load queries that you've previously saved by clicking the 'Open Query' button. |
SELECT MessageID, MsgControl, MsgType, MsgEvent, LastLoaded, SegmentCount, MessageSize,Inbound, Processed, Warnings FROM V23_HL7Data WHERE Loaded <> 0 AND Inbound <> 0 AND (LastLoaded BETWEEN '2007-12-19 00:00:00' AND '2008-01-20 00:00:00') AND Processed = 0 AND MsgType = 'ADT' AND MsgEvent IN ('A08','A04','A28') AND MessageID IN (Select MessageID From V23_Segment_PID_A WHERE PID_F5_C1 IN ('Ortiz','Clark','Alvez')) AND MessageID IN ( ( Select Distinct MessageID From V23_Segment_MSH_A WHERE MSH_F3_C1 = 'MEDIC' )) ORDER BY LastLoaded |
See all of the messages returned by your query
The Query Results
Drill down to individual messages and segments
See the detail for each message
/* To select the message from the main message data table */ Select * From V23_HL7Data Where MessageID = '92936F2EBB9ED74E8C56418E1AD84B75'
/* To retrieve all message segments from the Message Manifest table */ Select * From V23_MessageManifest Where MessageID = '92936F2EBB9ED74E8C56418E1AD84B75' Order By SegmentIDX
/* To retrieve message data for a particular segment of a message */ Select * From V23_Segment_PID_A Where MessageID = '92936F2EBB9ED74E8C56418E1AD84B75' AND IDX = 3
/* To retrieve ALL segments of a particular type for a message, exclude the IDX column in the select statement */ Select * From V23_Segment_PID_A Where MessageID = '92936F2EBB9ED74E8C56418E1AD84B75' Order By IDX
|