Click to Zoom
Missing Data Columns. This happens when the HL7 definition used to create the Schema indicates that a particular HL7 field is made up of <x> components but the HL7 message actually has data in a higher component OR if your trading partner just decides to violate the standard and put data wherever they want (hey, it happens). If you look at the test results in the screen shots (see the image to the left) you will see this entry about a missing column:
Table: ABC_Segment_OBR_A (1 Missing Column)
Column Name: OBR_F20_C2
Exception: [OBR_F20_C2] is missing from table [ABC_Segment_OBR_A]
Data Detected: HNA_ACCN
Suggested SQL: ALTER TABLE ABC_Segment_OBR_A ADD OBR_F20_C2 Varchar(13) NULL;
What this result tells us that in the HL7 Vendor definition used to create the Schema tables field #20 of the OBR Segment is made up of a HL7 data type which only contains ONE (1) component. Thus, the Schema Engine only created 1 component data column (OBR_F20_C1). In at least 1 of the HL7 message(s) tested there was data in a 2nd component of Field #20 of an OBR Segment (HNA_ACCN). Now you have a decision to make:
Do you care that this column is missing? If not then just proceed to step 3c. There are many reasons why you might not care. For instance, if your mission is to IMPORT HL7 messages and you have no need in your mission for any data that might actually be in this column then do nothing. Just configure the Schema Engine to ignore missing columns (which is the default behavior) by making changes to your Schema Profile (See Schema Warnings). However, if you need to create an HL7 message for EXPORT that contains data in a Component #2 of Field #20 of the OBR segment then you really need to fix the problem.
IF you need to fix the problem: You can just click the Show SQL Fixes button (or select the Suggested SQL and right click), copy the SQL Statement(s) to the clipboard, paste them into a Query window in your database manager (MS SQL or MySQL) and run them. AFTER you run the SQL statements come back to the Test Your Schema window and click to re-test.
If your Schema is missing an entire HL7 field?. When you look at the segment data tables in your Schema you can see that the column names tell you where in the HL7 message the data that populates that column will come from. Consider the column shown above [OBR_F20_C2] this name tells you that the data in that column will come from Field #20, Component #2 of the OBR HL7 segment of your messages. The Schema Engine creates these columns based on the HL7 Vendor Definition you used to create your Schema. In the Vendor Definition there is a structure for each HL7 Segment which tells how many HL7 Fields there are (the F20 portion of the example column name), and within each HL7 Field how many Components there are (the C2 portion of the example column name). The testing engine can handle notifications and SQL Fix suggestions if there are missing or truncated records which represent COMPONENTS within an existing HL7 field. However, if there IS NO HL7 Field in the Vendor Definition, then there won't be any Columns defined in the database for that field number and thus no easy SQL only fix.
EXAMPLE: In HL7 version 2.5.1 the DG1 (Diagnosis) segment contains 21 HL7 fields. So in a SQL Schema based on HL7 version 2.5.1 there would be a table called ABC_Segment_DG1_A with lots of columns which would END at column DG1_F21_C1. If you run a test with an HL7 message which for some reason contained data all the way out in Field #24 not only would there be no columns to hold that data BUT you would also be missing all columns for Field #22 and Field #23 as well. Below is a sample of what the test result would look like for this:
Table: ABC_Segment_DG1_A (1 Missing Column)
Column Name: DG1_F24_C0
Exception: There are no data columns in Segment [DG1] for Field 24 in this schema
Data Detected: 6Chars
Suggested SQL: No SQL Fix. Modify the HL7 Vendor Definition for Segment [DG1] to accommodate up to HL7 Field # 24, Repair your Schema, and Test again.
As shown we cannot create a SQL fiix for you in this scenario you would need to follow the instructions to edit the HL7 Vendor Definition to correct the problem. Then Repair Your Schema so that the new field definition(s) have Columns created for them. Finally run your test again to make sure that you didn't miss any columns, or have any truncation issues.
IMPORTANT NOTE: IF you use the Suggested SQL Fixes presented to you in the test results we strongly suggest that you SAVE those SQL Scripts for later use. In the future you may need to drop this Schema and recreate it. You can create your own new internal process for dealing with this which looks like this:
STEP 1: Click the Create Schema Tables in the UltraPort SQL Schema Engine (See Creating Schema Tables)
STEP 2: Run the SQL Script that I saved from my initial testing of the Schema which contains all of the changes that I need.
A Note about the Suggested SQL and how it works: You will see in the example above that the missing column was detected and that the data it found was (HNA_ACCN). This data represents the LONGEST piece of data detected in ALL of the messages tested for OBR 20.2. If you count you will also note this data is only 8 characters (bytes) and that in the Suggested SQL it recommends adding the column as a Varchar(13). This happens because the testing engine will ALWAYS add 5 bytes to the recommended size presented to you and it will NEVER suggest creating a column smaller than Varchar(10).