When you use the Schema Engine to create your schema engine it generates MySQL Scripts internally and executes them. You can also generate these SQL scripts yourself (see Creating Tables).
Important Note: The SQL Scripts are provided for your reference only. You should not attempt to manually create a schema yourself by generating the SQL scripts and then executing them in a SQL Query tool. The Schema Engine, in addition to creating the tables will also populate some of the master tables with information needed for the schema processors to run correctly.
These are the master tables used by the schema engine
DROP TABLE IF EXISTS `V23_HL7Data`;
CREATE TABLE `V23_HL7Data` ( MessageID Varchar(50) Not Null PRIMARY KEY, VendorName VarChar(50) Not Null, VendorVersion VarChar(25) Not Null, MsgControl VarChar(50) Not Null, PartnerAPP Varchar(60) Not Null, DateLoaded DateTime, LastLoaded DateTime, LoadCount Int Default 1, MsgType VarChar(20) Not Null, MsgEvent VarChar(20) Null, Outbound Int Not Null Default 0, Inbound Int Not Null Default 0, Processed Int Not Null Default 0, Warnings Int Null Default 0, Loaded Int Not Null Default 0, SchemaLoaded Int Not Null Default 0, StatusMessage VarChar(1024) Null, ArchiveID VarChar(50) Null, HL7Format Int Default 0, SegmentCount Int Default 1, MessageSize Int Default 0, HL7Message LONGTEXT Null );
CREATE INDEX `V23_HL7_AppControl` ON `V23_HL7Data` (MsgControl,PartnerApp);
DROP TABLE IF EXISTS `V23_MessageErrors`;
CREATE TABLE `V23_MessageErrors` ( MessageID Varchar(50) Not Null, DateLoaded DateTime, ErrorMessage Varchar(1024) Null );
CREATE INDEX `V23_MsgID` ON `V23_MessageErrors` (MessageID);
DROP TABLE IF EXISTS `V23_SchemaMaster`;
CREATE TABLE `V23_SchemaMaster` ( Schema_ID Varchar(50) Not Null PRIMARY KEY, Schema_VendorID Varchar(50) Not Null, Maint_LastRun DateTime, Created DateTime, VendorDefinition LONGTEXT Null );
DROP TABLE IF EXISTS `V23_MessageManifest`;
CREATE TABLE `V23_MessageManifest` ( MessageID Varchar(50) Not Null, SegmentName VarChar(10) Not Null, SegmentIDX int Not Null ) ;
CREATE UNIQUE INDEX `V23_Manifest` ON `V23_MessageManifest` (MessageID,SegmentIDX);
DROP TABLE IF EXISTS `V23_InterfaceTables`;
CREATE TABLE `V23_InterfaceTables` ( `SegmentName` VarChar(10) Not Null PRIMARY KEY, `TableNames` VarChar(75) Not Null, `Hl7FieldCount` int Not Null, `Description` VarChar (1024) Null ) ;
DROP TABLE IF EXISTS `V23_InterfaceFields`;
CREATE TABLE `V23_InterfaceFields` ( `Record_GUID` Varchar(50) Not Null PRIMARY KEY, `SegmentName` VarChar(10) Not Null, `TableName` VarChar(50) Not Null, `FieldNumber` int Not Null, `Components` int Not Null, `Repeats` int Not Null, `Description` VarChar(1024) Null ) ;
DROP TABLE IF EXISTS `V23_InterfaceComponents`;
CREATE TABLE `V23_InterfaceComponents` ( `Record_GUID` Varchar(50) Not Null PRIMARY KEY, `SegmentName` VarChar(10) Not Null, `FieldNumber` int Not Null, `FieldName` VarChar (30) Not Null, `Component` int Not Null, `SubComponents` int Not Null, `Description` VarChar(1024) Null ) ;
|
This is a partial example of the SQL generated to create some of the HL7 segment database tables for a schema with a prefix of V23 based on the default HL7 Version 2.3 vendor definition.
Table names are dynamically created based on the schema prefix, the HL7 segment name and the number of HL7 fields the segment contains. Like so:
<PREFIX>_Segment_<HL7SEGMENTNAME>_A
If a segment contains 40 or more HL7 data fields it will be split into 2 tables with the data in fields 1-39 going into table <PREFIX>_Segment_<HL7SEGMENTNAME>_A and fields 40 to <fieldcount> going into table <PREFIX>_Segment_<HL7SEGMENTNAME>_B
/* Drop Table V23_Segment_ACC_A */ DROP TABLE IF EXISTS `V23_Segment_ACC_A`;
/* Create the table for the ACC segment */ CREATE TABLE `V23_Segment_ACC_A` ( `MessageID` varchar(50) NOT NULL, `IDX` INT NOT NULL, `ACC_F1_C1` varchar(26) NULL, `ACC_F1_C2` varchar(26) NULL, `ACC_F2_C1` varchar(40) NULL, `ACC_F2_C2` varchar(40) NULL, `ACC_F2_C3` varchar(40) NULL, `ACC_F2_C4` varchar(40) NULL, `ACC_F2_C5` varchar(40) NULL, `ACC_F2_C6` varchar(40) NULL, `ACC_F3_C1` varchar(25) NULL, `ACC_F4_C1` varchar(40) NULL, `ACC_F4_C2` varchar(40) NULL, `ACC_F4_C3` varchar(40) NULL, `ACC_F4_C4` varchar(40) NULL, `ACC_F4_C5` varchar(40) NULL, `ACC_F4_C6` varchar(40) NULL, `ACC_F5_C1` varchar(10) NULL, `ACC_F6_C1` varchar(12) NULL );
CREATE UNIQUE INDEX EH_ACC1_MSGIDX on `V23_Segment_ACC_A` (MessageID,IDX);
/* Drop Table V23_Segment_ADD_A */ DROP TABLE IF EXISTS `V23_Segment_ADD_A`;
/* Create the table for the ADD segment */ CREATE TABLE `V23_Segment_ADD_A` ( `MessageID` varchar(50) NOT NULL, `IDX` INT NOT NULL, `ADD_F1_C1` LONGTEXT NULL );
CREATE UNIQUE INDEX EH_ADD1_MSGIDX on `V23_Segment_ADD_A` (MessageID,IDX);
/* Drop Table V23_Segment_AIG_A */ DROP TABLE IF EXISTS `V23_Segment_AIG_A`;
/* Create the table for the AIG segment */ CREATE TABLE `V23_Segment_AIG_A` ( `MessageID` varchar(50) NOT NULL, `IDX` INT NOT NULL, `AIG_F1_C1` varchar(10) NULL, `AIG_F2_C1` varchar(10) NULL, `AIG_F3_C1` varchar(40) NULL, `AIG_F3_C2` varchar(40) NULL, `AIG_F3_C3` varchar(40) NULL, `AIG_F3_C4` varchar(40) NULL, `AIG_F3_C5` varchar(40) NULL, `AIG_F3_C6` varchar(40) NULL, `AIG_F4_C1` varchar(40) NULL, `AIG_F4_C2` varchar(40) NULL, `AIG_F4_C3` varchar(40) NULL, `AIG_F4_C4` varchar(40) NULL, `AIG_F4_C5` varchar(40) NULL, `AIG_F4_C6` varchar(40) NULL, `AIG_F5_C1` varchar(40) NULL, `AIG_F5_C2` varchar(40) NULL, `AIG_F5_C3` varchar(40) NULL, `AIG_F5_C4` varchar(40) NULL, `AIG_F5_C5` varchar(40) NULL, `AIG_F5_C6` varchar(40) NULL, `AIG_F6_C1` varchar(10) NULL, `AIG_F7_C1` varchar(40) NULL, `AIG_F7_C2` varchar(40) NULL, `AIG_F7_C3` varchar(40) NULL, `AIG_F7_C4` varchar(40) NULL, `AIG_F7_C5` varchar(40) NULL, `AIG_F7_C6` varchar(40) NULL, `AIG_F8_C1` varchar(26) NULL, `AIG_F8_C2` varchar(26) NULL, `AIG_F9_C1` varchar(20) NULL, `AIG_F10_C1` varchar(40) NULL, `AIG_F10_C2` varchar(40) NULL, `AIG_F10_C3` varchar(40) NULL, `AIG_F10_C4` varchar(40) NULL, `AIG_F10_C5` varchar(40) NULL, `AIG_F10_C6` varchar(40) NULL, `AIG_F11_C1` varchar(20) NULL, `AIG_F12_C1` varchar(40) NULL, `AIG_F12_C2` varchar(40) NULL, `AIG_F12_C3` varchar(40) NULL, `AIG_F12_C4` varchar(40) NULL, `AIG_F12_C5` varchar(40) NULL, `AIG_F12_C6` varchar(40) NULL, `AIG_F13_C1` varchar(10) NULL, `AIG_F14_C1` varchar(40) NULL, `AIG_F14_C2` varchar(40) NULL, `AIG_F14_C3` varchar(40) NULL, `AIG_F14_C4` varchar(40) NULL, `AIG_F14_C5` varchar(40) NULL, `AIG_F14_C6` varchar(40) NULL );
CREATE UNIQUE INDEX EH_AIG1_MSGIDX on `V23_Segment_AIG_A` (MessageID,IDX);
/* Drop Table V23_Segment_AIL_A */ DROP TABLE IF EXISTS `V23_Segment_AIL_A`;
/* Create the table for the AIL segment */ CREATE TABLE `V23_Segment_AIL_A` ( `MessageID` varchar(50) NOT NULL, `IDX` INT NOT NULL, `AIL_F1_C1` varchar(10) NULL, `AIL_F2_C1` varchar(10) NULL, `AIL_F3_C1` varchar(20) NULL, `AIL_F3_C2` varchar(20) NULL, `AIL_F3_C3` varchar(20) NULL, `AIL_F3_C4` varchar(20) NULL, `AIL_F3_C5` varchar(20) NULL, `AIL_F3_C6` varchar(20) NULL, `AIL_F3_C7` varchar(20) NULL, `AIL_F3_C8` varchar(20) NULL, `AIL_F3_C9` varchar(20) NULL, `AIL_F4_C1` varchar(40) NULL, `AIL_F4_C2` varchar(40) NULL, `AIL_F4_C3` varchar(40) NULL, `AIL_F4_C4` varchar(40) NULL, `AIL_F4_C5` varchar(40) NULL, `AIL_F4_C6` varchar(40) NULL, `AIL_F5_C1` varchar(40) NULL, `AIL_F5_C2` varchar(40) NULL, `AIL_F5_C3` varchar(40) NULL, `AIL_F5_C4` varchar(40) NULL, `AIL_F5_C5` varchar(40) NULL, `AIL_F5_C6` varchar(40) NULL, `AIL_F6_C1` varchar(26) NULL, `AIL_F6_C2` varchar(26) NULL, `AIL_F7_C1` varchar(20) NULL, `AIL_F8_C1` varchar(40) NULL, `AIL_F8_C2` varchar(40) NULL, `AIL_F8_C3` varchar(40) NULL, `AIL_F8_C4` varchar(40) NULL, `AIL_F8_C5` varchar(40) NULL, `AIL_F8_C6` varchar(40) NULL, `AIL_F9_C1` varchar(20) NULL, `AIL_F10_C1` varchar(40) NULL, `AIL_F10_C2` varchar(40) NULL, `AIL_F10_C3` varchar(40) NULL, `AIL_F10_C4` varchar(40) NULL, `AIL_F10_C5` varchar(40) NULL, `AIL_F10_C6` varchar(40) NULL, `AIL_F11_C1` varchar(10) NULL, `AIL_F12_C1` varchar(40) NULL, `AIL_F12_C2` varchar(40) NULL, `AIL_F12_C3` varchar(40) NULL, `AIL_F12_C4` varchar(40) NULL, `AIL_F12_C5` varchar(40) NULL, `AIL_F12_C6` varchar(40) NULL );
CREATE UNIQUE INDEX EH_AIL1_MSGIDX on `V23_Segment_AIL_A` (MessageID,IDX);
/* Drop Table V23_Segment_AIP_A */ DROP TABLE IF EXISTS `V23_Segment_AIP_A`;
/* Create the table for the AIP segment */ CREATE TABLE `V23_Segment_AIP_A` ( `MessageID` varchar(50) NOT NULL, `IDX` INT NOT NULL, `AIP_F1_C1` varchar(10) NULL, `AIP_F2_C1` varchar(10) NULL, `AIP_F3_C1` varchar(35) NULL, `AIP_F3_C2` varchar(35) NULL, `AIP_F3_C3` varchar(35) NULL, `AIP_F3_C4` varchar(35) NULL, `AIP_F3_C5` varchar(35) NULL, `AIP_F3_C6` varchar(35) NULL, `AIP_F3_C7` varchar(35) NULL, `AIP_F4_C1` varchar(40) NULL, `AIP_F4_C2` varchar(40) NULL, `AIP_F4_C3` varchar(40) NULL, `AIP_F4_C4` varchar(40) NULL, `AIP_F4_C5` varchar(40) NULL, `AIP_F4_C6` varchar(40) NULL, `AIP_F5_C1` varchar(40) NULL, `AIP_F5_C2` varchar(40) NULL, `AIP_F5_C3` varchar(40) NULL, `AIP_F5_C4` varchar(40) NULL, `AIP_F5_C5` varchar(40) NULL, `AIP_F5_C6` varchar(40) NULL, `AIP_F6_C1` varchar(26) NULL, `AIP_F6_C2` varchar(26) NULL, `AIP_F7_C1` varchar(20) NULL, `AIP_F8_C1` varchar(40) NULL, `AIP_F8_C2` varchar(40) NULL, `AIP_F8_C3` varchar(40) NULL, `AIP_F8_C4` varchar(40) NULL, `AIP_F8_C5` varchar(40) NULL, `AIP_F8_C6` varchar(40) NULL, `AIP_F9_C1` varchar(20) NULL, `AIP_F10_C1` varchar(40) NULL, `AIP_F10_C2` varchar(40) NULL, `AIP_F10_C3` varchar(40) NULL, `AIP_F10_C4` varchar(40) NULL, `AIP_F10_C5` varchar(40) NULL, `AIP_F10_C6` varchar(40) NULL, `AIP_F11_C1` varchar(10) NULL, `AIP_F12_C1` varchar(40) NULL, `AIP_F12_C2` varchar(40) NULL, `AIP_F12_C3` varchar(40) NULL, `AIP_F12_C4` varchar(40) NULL, `AIP_F12_C5` varchar(40) NULL, `AIP_F12_C6` varchar(40) NULL );
CREATE UNIQUE INDEX EH_AIP1_MSGIDX on `V23_Segment_AIP_A` (MessageID,IDX);
/* Drop Table V23_Segment_GT1_A */ DROP TABLE IF EXISTS `V23_Segment_GT1_A`;
/* Drop Table V23_Segment_GT1_B */ DROP TABLE IF EXISTS `V23_Segment_GT1_B`;
/* Create the table for the GT1 segment */ CREATE TABLE `V23_Segment_GT1_A` ( `MessageID` varchar(50) NOT NULL, `IDX` INT NOT NULL, `GT1_F1_C1` varchar(10) NULL, `GT1_F2_C1` varchar(59) NULL, `GT1_F2_C2` varchar(59) NULL, `GT1_F2_C3` varchar(20) NULL, `GT1_F2_C4` varchar(20) NULL, `GT1_F2_C5` varchar(20) NULL, `GT1_F2_C6` varchar(20) NULL, `GT1_F3_C1` varchar(35) NULL, `GT1_F3_C2` varchar(35) NULL, `GT1_F3_C3` varchar(35) NULL, `GT1_F3_C4` varchar(35) NULL, `GT1_F3_C5` varchar(35) NULL, `GT1_F3_C6` varchar(35) NULL, `GT1_F3_C7` varchar(20) NULL, `GT1_F4_C1` varchar(35) NULL, `GT1_F4_C2` varchar(35) NULL, `GT1_F4_C3` varchar(35) NULL, `GT1_F4_C4` varchar(35) NULL, `GT1_F4_C5` varchar(35) NULL, `GT1_F4_C6` varchar(35) NULL, `GT1_F4_C7` varchar(20) NULL, `GT1_F5_C1` varchar(40) NULL, `GT1_F5_C2` varchar(40) NULL, `GT1_F5_C3` varchar(40) NULL, `GT1_F5_C4` varchar(40) NULL, `GT1_F5_C5` varchar(40) NULL, `GT1_F5_C6` varchar(20) NULL, `GT1_F5_C7` varchar(20) NULL, `GT1_F5_C8` varchar(20) NULL, `GT1_F5_C9` varchar(20) NULL, `GT1_F6_C1` varchar(20) NULL, `GT1_F6_C2` varchar(20) NULL, `GT1_F6_C3` varchar(20) NULL, `GT1_F6_C4` varchar(20) NULL, `GT1_F6_C5` varchar(20) NULL, `GT1_F6_C6` varchar(20) NULL, `GT1_F6_C7` varchar(20) NULL, `GT1_F6_C8` varchar(20) NULL, `GT1_F7_C1` varchar(20) NULL, `GT1_F7_C2` varchar(20) NULL, `GT1_F7_C3` varchar(20) NULL, `GT1_F7_C4` varchar(20) NULL, `GT1_F7_C5` varchar(20) NULL, `GT1_F7_C6` varchar(20) NULL, `GT1_F7_C7` varchar(20) NULL, `GT1_F7_C8` varchar(20) NULL, `GT1_F8_C1` varchar(26) NULL, `GT1_F8_C2` varchar(26) NULL, `GT1_F9_C1` varchar(10) NULL, `GT1_F10_C1` varchar(10) NULL, `GT1_F11_C1` varchar(40) NULL, `GT1_F11_C2` varchar(40) NULL, `GT1_F11_C3` varchar(40) NULL, `GT1_F11_C4` varchar(40) NULL, `GT1_F11_C5` varchar(40) NULL, `GT1_F11_C6` varchar(40) NULL, `GT1_F12_C1` varchar(11) NULL, `GT1_F13_C1` varchar(10) NULL, `GT1_F14_C1` varchar(10) NULL, `GT1_F15_C1` varchar(10) NULL, `GT1_F16_C1` varchar(35) NULL, `GT1_F16_C2` varchar(35) NULL, `GT1_F16_C3` varchar(35) NULL, `GT1_F16_C4` varchar(35) NULL, `GT1_F16_C5` varchar(35) NULL, `GT1_F16_C6` varchar(35) NULL, `GT1_F16_C7` varchar(20) NULL, `GT1_F17_C1` varchar(40) NULL, `GT1_F17_C2` varchar(40) NULL, `GT1_F17_C3` varchar(40) NULL, `GT1_F17_C4` varchar(40) NULL, `GT1_F17_C5` varchar(40) NULL, `GT1_F17_C6` varchar(20) NULL, `GT1_F17_C7` varchar(20) NULL, `GT1_F17_C8` varchar(20) NULL, `GT1_F17_C9` varchar(20) NULL, `GT1_F18_C1` varchar(20) NULL, `GT1_F18_C2` varchar(20) NULL, `GT1_F18_C3` varchar(20) NULL, `GT1_F18_C4` varchar(20) NULL, `GT1_F18_C5` varchar(20) NULL, `GT1_F18_C6` varchar(20) NULL, `GT1_F18_C7` varchar(20) NULL, `GT1_F18_C8` varchar(20) NULL, `GT1_F19_C1` varchar(20) NULL, `GT1_F19_C2` varchar(20) NULL, `GT1_F19_C3` varchar(20) NULL, `GT1_F19_C4` varchar(20) NULL, `GT1_F19_C5` varchar(20) NULL, `GT1_F19_C6` varchar(20) NULL, `GT1_F20_C1` varchar(10) NULL, `GT1_F21_C1` varchar(50) NULL, `GT1_F21_C2` varchar(20) NULL, `GT1_F21_C3` varchar(20) NULL, `GT1_F21_C4` varchar(20) NULL, `GT1_F21_C5` varchar(20) NULL, `GT1_F21_C6` varchar(20) NULL, `GT1_F21_C7` varchar(20) NULL, `GT1_F21_C8` varchar(20) NULL, `GT1_F22_C1` varchar(10) NULL, `GT1_F23_C1` varchar(40) NULL, `GT1_F23_C2` varchar(40) NULL, `GT1_F23_C3` varchar(40) NULL, `GT1_F23_C4` varchar(40) NULL, `GT1_F23_C5` varchar(40) NULL, `GT1_F23_C6` varchar(40) NULL, `GT1_F24_C1` varchar(26) NULL, `GT1_F24_C2` varchar(26) NULL, `GT1_F25_C1` varchar(10) NULL, `GT1_F26_C1` varchar(40) NULL, `GT1_F26_C2` varchar(40) NULL, `GT1_F26_C3` varchar(40) NULL, `GT1_F26_C4` varchar(40) NULL, `GT1_F26_C5` varchar(40) NULL, `GT1_F26_C6` varchar(40) NULL, `GT1_F27_C1` varchar(10) NULL, `GT1_F27_C2` varchar(10) NULL, `GT1_F27_C3` varchar(10) NULL, `GT1_F27_C4` varchar(10) NULL, `GT1_F27_C5` varchar(10) NULL, `GT1_F27_C6` varchar(10) NULL, `GT1_F28_C1` varchar(10) NULL, `GT1_F29_C1` varchar(20) NULL, `GT1_F29_C2` varchar(20) NULL, `GT1_F29_C3` varchar(20) NULL, `GT1_F29_C4` varchar(20) NULL, `GT1_F29_C5` varchar(20) NULL, `GT1_F29_C6` varchar(20) NULL, `GT1_F30_C1` varchar(40) NULL, `GT1_F30_C2` varchar(40) NULL, `GT1_F30_C3` varchar(40) NULL, `GT1_F30_C4` varchar(40) NULL, `GT1_F30_C5` varchar(40) NULL, `GT1_F30_C6` varchar(40) NULL, `GT1_F31_C1` varchar(10) NULL, `GT1_F32_C1` varchar(10) NULL, `GT1_F33_C1` varchar(10) NULL, `GT1_F34_C1` varchar(10) NULL, `GT1_F35_C1` varchar(40) NULL, `GT1_F35_C2` varchar(40) NULL, `GT1_F35_C3` varchar(40) NULL, `GT1_F35_C4` varchar(40) NULL, `GT1_F35_C5` varchar(40) NULL, `GT1_F35_C6` varchar(40) NULL, `GT1_F36_C1` varchar(40) NULL, `GT1_F36_C2` varchar(40) NULL, `GT1_F36_C3` varchar(40) NULL, `GT1_F36_C4` varchar(40) NULL, `GT1_F36_C5` varchar(40) NULL, `GT1_F36_C6` varchar(40) NULL, `GT1_F37_C1` varchar(10) NULL, `GT1_F38_C1` varchar(40) NULL, `GT1_F38_C2` varchar(40) NULL, `GT1_F38_C3` varchar(40) NULL, `GT1_F38_C4` varchar(40) NULL, `GT1_F38_C5` varchar(40) NULL, `GT1_F38_C6` varchar(40) NULL, `GT1_F39_C1` varchar(10) NULL, `GT1_F40_C1` varchar(10) NULL );
CREATE UNIQUE INDEX EH_GT11_MSGIDX on `V23_Segment_GT1_A` (MessageID,IDX);
CREATE TABLE `V23_Segment_GT1_B` ( `MessageID` varchar(50) NOT NULL, `IDX` INT NOT NULL, `GT1_F41_C1` varchar(40) NULL, `GT1_F41_C2` varchar(40) NULL, `GT1_F41_C3` varchar(40) NULL, `GT1_F41_C4` varchar(40) NULL, `GT1_F41_C5` varchar(40) NULL, `GT1_F41_C6` varchar(40) NULL, `GT1_F42_C1` varchar(35) NULL, `GT1_F42_C2` varchar(35) NULL, `GT1_F42_C3` varchar(35) NULL, `GT1_F42_C4` varchar(35) NULL, `GT1_F42_C5` varchar(35) NULL, `GT1_F42_C6` varchar(35) NULL, `GT1_F42_C7` varchar(20) NULL, `GT1_F43_C1` varchar(40) NULL, `GT1_F43_C2` varchar(40) NULL, `GT1_F43_C3` varchar(40) NULL, `GT1_F43_C4` varchar(40) NULL, `GT1_F43_C5` varchar(40) NULL, `GT1_F43_C6` varchar(40) NULL, `GT1_F44_C1` varchar(40) NULL, `GT1_F44_C2` varchar(40) NULL, `GT1_F44_C3` varchar(40) NULL, `GT1_F44_C4` varchar(40) NULL, `GT1_F44_C5` varchar(40) NULL, `GT1_F44_C6` varchar(40) NULL, `GT1_F45_C1` varchar(35) NULL, `GT1_F45_C2` varchar(35) NULL, `GT1_F45_C3` varchar(35) NULL, `GT1_F45_C4` varchar(35) NULL, `GT1_F45_C5` varchar(35) NULL, `GT1_F45_C6` varchar(35) NULL, `GT1_F45_C7` varchar(20) NULL, `GT1_F46_C1` varchar(20) NULL, `GT1_F46_C2` varchar(20) NULL, `GT1_F46_C3` varchar(20) NULL, `GT1_F46_C4` varchar(20) NULL, `GT1_F46_C5` varchar(20) NULL, `GT1_F46_C6` varchar(20) NULL, `GT1_F46_C7` varchar(20) NULL, `GT1_F46_C8` varchar(20) NULL, `GT1_F47_C1` varchar(40) NULL, `GT1_F47_C2` varchar(40) NULL, `GT1_F47_C3` varchar(40) NULL, `GT1_F47_C4` varchar(40) NULL, `GT1_F47_C5` varchar(40) NULL, `GT1_F47_C6` varchar(40) NULL, `GT1_F48_C1` varchar(10) NULL, `GT1_F49_C1` varchar(20) NULL, `GT1_F50_C1` varchar(20) NULL, `GT1_F50_C2` varchar(20) NULL, `GT1_F51_C1` varchar(50) NULL, `GT1_F51_C2` varchar(20) NULL, `GT1_F51_C3` varchar(20) NULL, `GT1_F51_C4` varchar(20) NULL, `GT1_F51_C5` varchar(20) NULL, `GT1_F51_C6` varchar(20) NULL, `GT1_F51_C7` varchar(20) NULL, `GT1_F51_C8` varchar(20) NULL, `GT1_F52_C1` varchar(10) NULL, `GT1_F53_C1` varchar(10) NULL, `GT1_F54_C1` varchar(20) NULL, `GT1_F54_C2` varchar(20) NULL, `GT1_F55_C1` varchar(40) NULL, `GT1_F55_C2` varchar(40) NULL, `GT1_F55_C3` varchar(40) NULL, `GT1_F55_C4` varchar(40) NULL, `GT1_F55_C5` varchar(40) NULL, `GT1_F55_C6` varchar(40) NULL );
CREATE UNIQUE INDEX EH_GT12_MSGIDX on `V23_Segment_GT1_B` (MessageID,IDX);
/* Drop Table V23_Segment_MSH_A */ DROP TABLE IF EXISTS `V23_Segment_MSH_A`;
/* Create the table for the MSH segment */ CREATE TABLE `V23_Segment_MSH_A` ( `MessageID` varchar(50) NOT NULL, `IDX` INT NOT NULL, `MSH_F1_C1` varchar(50) NULL, `MSH_F2_C1` varchar(50) NULL, `MSH_F3_C1` varchar(50) NULL, `MSH_F3_C2` varchar(50) NULL, `MSH_F3_C3` varchar(50) NULL, `MSH_F4_C1` varchar(50) NULL, `MSH_F4_C2` varchar(50) NULL, `MSH_F4_C3` varchar(50) NULL, `MSH_F5_C1` varchar(50) NULL, `MSH_F5_C2` varchar(50) NULL, `MSH_F5_C3` varchar(50) NULL, `MSH_F6_C1` varchar(50) NULL, `MSH_F6_C2` varchar(50) NULL, `MSH_F6_C3` varchar(50) NULL, `MSH_F7_C1` varchar(50) NULL, `MSH_F7_C2` varchar(50) NULL, `MSH_F8_C1` varchar(50) NULL, `MSH_F9_C1` varchar(50) NULL, `MSH_F9_C2` varchar(50) NULL, `MSH_F9_C3` varchar(50) NULL, `MSH_F9_C4` varchar(50) NULL, `MSH_F10_C1` varchar(50) NULL, `MSH_F11_C1` varchar(50) NULL, `MSH_F11_C2` varchar(50) NULL, `MSH_F12_C1` varchar(50) NULL, `MSH_F12_C2` varchar(50) NULL, `MSH_F12_C3` varchar(50) NULL, `MSH_F13_C1` varchar(50) NULL, `MSH_F14_C1` varchar(180) NULL, `MSH_F15_C1` varchar(50) NULL, `MSH_F16_C1` varchar(50) NULL, `MSH_F17_C1` varchar(50) NULL, `MSH_F18_C1` varchar(50) NULL, `MSH_F19_C1` varchar(50) NULL, `MSH_F19_C2` varchar(50) NULL, `MSH_F19_C3` varchar(50) NULL, `MSH_F19_C4` varchar(50) NULL, `MSH_F19_C5` varchar(50) NULL, `MSH_F19_C6` varchar(50) NULL, `MSH_F20_C1` varchar(50) NULL );
CREATE UNIQUE INDEX EH_MSH1_MSGIDX on `V23_Segment_MSH_A` (MessageID,IDX);
CREATE INDEX IDX_MSH_F9_C1 ON `V23_Segment_MSH_A`(`MSH_F9_C1`);
CREATE INDEX IDX_MSH_F9_C2 ON `V23_Segment_MSH_A`(`MSH_F9_C2`);
CREATE INDEX IDX_MSH_F10_C1 ON `V23_Segment_MSH_A`(`MSH_F10_C1`);
/* Drop Table V23_Segment_PID_A */ DROP TABLE IF EXISTS `V23_Segment_PID_A`;
/* Create the table for the PID segment */ CREATE TABLE `V23_Segment_PID_A` ( `MessageID` varchar(50) NOT NULL, `IDX` INT NOT NULL, `PID_F1_C1` varchar(10) NULL, `PID_F2_C1` varchar(20) NULL, `PID_F2_C2` varchar(20) NULL, `PID_F2_C3` varchar(20) NULL, `PID_F2_C4` varchar(20) NULL, `PID_F2_C5` varchar(20) NULL, `PID_F2_C6` varchar(20) NULL, `PID_F3_C1` varchar(20) NULL, `PID_F3_C2` varchar(20) NULL, `PID_F3_C3` varchar(20) NULL, `PID_F3_C4` varchar(20) NULL, `PID_F3_C5` varchar(20) NULL, `PID_F3_C6` varchar(20) NULL, `PID_F4_C1` varchar(20) NULL, `PID_F4_C2` varchar(20) NULL, `PID_F4_C3` varchar(20) NULL, `PID_F4_C4` varchar(20) NULL, `PID_F4_C5` varchar(20) NULL, `PID_F4_C6` varchar(20) NULL, `PID_F5_C1` varchar(35) NULL, `PID_F5_C2` varchar(35) NULL, `PID_F5_C3` varchar(35) NULL, `PID_F5_C4` varchar(35) NULL, `PID_F5_C5` varchar(35) NULL, `PID_F5_C6` varchar(35) NULL, `PID_F5_C7` varchar(20) NULL, `PID_F6_C1` varchar(35) NULL, `PID_F6_C2` varchar(35) NULL, `PID_F6_C3` varchar(35) NULL, `PID_F6_C4` varchar(35) NULL, `PID_F6_C5` varchar(35) NULL, `PID_F6_C6` varchar(35) NULL, `PID_F6_C7` varchar(20) NULL, `PID_F7_C1` varchar(26) NULL, `PID_F7_C2` varchar(26) NULL, `PID_F8_C1` varchar(10) NULL, `PID_F9_C1` varchar(35) NULL, `PID_F9_C2` varchar(35) NULL, `PID_F9_C3` varchar(35) NULL, `PID_F9_C4` varchar(35) NULL, `PID_F9_C5` varchar(35) NULL, `PID_F9_C6` varchar(35) NULL, `PID_F9_C7` varchar(20) NULL, `PID_F10_C1` varchar(40) NULL, `PID_F10_C2` varchar(40) NULL, `PID_F10_C3` varchar(40) NULL, `PID_F10_C4` varchar(40) NULL, `PID_F10_C5` varchar(40) NULL, `PID_F10_C6` varchar(40) NULL, `PID_F11_C1` varchar(40) NULL, `PID_F11_C2` varchar(40) NULL, `PID_F11_C3` varchar(40) NULL, `PID_F11_C4` varchar(40) NULL, `PID_F11_C5` varchar(40) NULL, `PID_F11_C6` varchar(20) NULL, `PID_F11_C7` varchar(20) NULL, `PID_F11_C8` varchar(20) NULL, `PID_F11_C9` varchar(20) NULL, `PID_F12_C1` varchar(10) NULL, `PID_F13_C1` varchar(20) NULL, `PID_F13_C2` varchar(20) NULL, `PID_F13_C3` varchar(20) NULL, `PID_F13_C4` varchar(20) NULL, `PID_F13_C5` varchar(20) NULL, `PID_F13_C6` varchar(20) NULL, `PID_F13_C7` varchar(20) NULL, `PID_F13_C8` varchar(20) NULL, `PID_F14_C1` varchar(20) NULL, `PID_F14_C2` varchar(20) NULL, `PID_F14_C3` varchar(20) NULL, `PID_F14_C4` varchar(20) NULL, `PID_F14_C5` varchar(20) NULL, `PID_F14_C6` varchar(20) NULL, `PID_F14_C7` varchar(20) NULL, `PID_F14_C8` varchar(20) NULL, `PID_F15_C1` varchar(40) NULL, `PID_F15_C2` varchar(40) NULL, `PID_F15_C3` varchar(40) NULL, `PID_F15_C4` varchar(40) NULL, `PID_F15_C5` varchar(40) NULL, `PID_F15_C6` varchar(40) NULL, `PID_F16_C1` varchar(40) NULL, `PID_F16_C2` varchar(40) NULL, `PID_F16_C3` varchar(40) NULL, `PID_F16_C4` varchar(40) NULL, `PID_F16_C5` varchar(40) NULL, `PID_F16_C6` varchar(40) NULL, `PID_F17_C1` varchar(40) NULL, `PID_F17_C2` varchar(40) NULL, `PID_F17_C3` varchar(40) NULL, `PID_F17_C4` varchar(40) NULL, `PID_F17_C5` varchar(40) NULL, `PID_F17_C6` varchar(40) NULL, `PID_F18_C1` varchar(20) NULL, `PID_F18_C2` varchar(20) NULL, `PID_F18_C3` varchar(20) NULL, `PID_F18_C4` varchar(20) NULL, `PID_F18_C5` varchar(20) NULL, `PID_F18_C6` varchar(20) NULL, `PID_F19_C1` varchar(16) NULL, `PID_F20_C1` varchar(25) NULL, `PID_F20_C2` varchar(20) NULL, `PID_F20_C3` varchar(20) NULL, `PID_F21_C1` varchar(20) NULL, `PID_F21_C2` varchar(20) NULL, `PID_F21_C3` varchar(20) NULL, `PID_F21_C4` varchar(20) NULL, `PID_F21_C5` varchar(20) NULL, `PID_F21_C6` varchar(20) NULL, `PID_F22_C1` varchar(40) NULL, `PID_F22_C2` varchar(40) NULL, `PID_F22_C3` varchar(40) NULL, `PID_F22_C4` varchar(40) NULL, `PID_F22_C5` varchar(40) NULL, `PID_F22_C6` varchar(40) NULL, `PID_F23_C1` varchar(60) NULL, `PID_F24_C1` varchar(10) NULL, `PID_F25_C1` varchar(10) NULL, `PID_F26_C1` varchar(40) NULL, `PID_F26_C2` varchar(40) NULL, `PID_F26_C3` varchar(40) NULL, `PID_F26_C4` varchar(40) NULL, `PID_F26_C5` varchar(40) NULL, `PID_F26_C6` varchar(40) NULL, `PID_F27_C1` varchar(40) NULL, `PID_F27_C2` varchar(40) NULL, `PID_F27_C3` varchar(40) NULL, `PID_F27_C4` varchar(40) NULL, `PID_F27_C5` varchar(40) NULL, `PID_F27_C6` varchar(40) NULL, `PID_F28_C1` varchar(40) NULL, `PID_F28_C2` varchar(40) NULL, `PID_F28_C3` varchar(40) NULL, `PID_F28_C4` varchar(40) NULL, `PID_F28_C5` varchar(40) NULL, `PID_F28_C6` varchar(40) NULL, `PID_F29_C1` varchar(26) NULL, `PID_F29_C2` varchar(26) NULL, `PID_F30_C1` varchar(10) NULL );
CREATE UNIQUE INDEX EH_PID1_MSGIDX on `V23_Segment_PID_A` (MessageID,IDX);
CREATE INDEX IDX_PID_F3_C1 ON `V23_Segment_PID_A`(`PID_F3_C1`);
CREATE INDEX IDX_PID_F5_C1 ON `V23_Segment_PID_A`(`PID_F5_C1`);
|