Insert statement incorrectly formatted

Help
2014-07-16
2014-07-16
  • Kevin James
    Kevin James
    2014-07-16

    SymmeticDS 3.5.5 seems to be incorrectly formatting insert. Column in position 41 in the target table is showing up in position 2 in the Symmetric log. Here are the messages from the Symmetric log:

    Failed sql was: insert into "EQB"."STARTSC" ("START_ID") values (?)
    Failed row data was: "6696510","2002","1074","2002-05-29 00:00:00","GLD","USA","D","4","97034544","4353084","Silent Pic","SILENTPIC","903","JE","Houghton","T.","D.","HOUGHTON","T","D","28451","TE","Londono, Sr.","Odin","J.","LONDONOSR
    ","ODIN","J","735172","O6","Saurbier","Marvin"," ","SAURBIER","MARVIN"," ","F","5","D","0","0","0","1","2520.00","-1"

    2014-07-16 10:20:23,005 ERROR [client-eqbora01] [DataLoaderService] [qtp1736734731-20] Failed to load batch server-266985 because: ORA-01400: cannot insert NULL into ("EQB"."STARTSC"."RACE_DATE")

    The value “1074” in the row data should be near the end like so:

    …"1074","1","2520.00","-1"

    The target is an Oracle database. The column in question is part of a table transform.

    Rows from sym_transform_column table:
    transform_id include_on target_column_name source_column_name pk transform_type transform_expression transform_order create_time last_update_by last_update_time
    startsc * ATTS attributes 0 copy [NULL] 2 2013-09-05T02:28:44 [NULL] 2013-09-05T02:28:44
    startsc * RACE_YEAR year 0 copy [NULL] 1 2013-09-05T02:28:44 [NULL] 2013-09-05T02:28:44

    Row from sym_transform_table table:
    transform_id source_node_group_id target_node_group_id transform_point source_catalog_name source_schema_name source_table_name target_catalog_name target_schema_name target_table_name update_first delete_action transform_order column_policy create_time last_update_by last_update_time
    startsc server client EXTRACT startsc [NULL] EQB STARTSC 0 DEL_ROW 0 IMPLIED 2013-09-05T14:27:52 [NULL] 2013-09-05T02:27:44

    So, not sure if the reformatting is the issue or if it is the transformation setup. The column is named attributes in the source, but named atts in the target table.

    Any help is appreciated.

     
  • Kevin James
    Kevin James
    2014-07-16

    Here are the sym_data rows for the failed batch:
    data_id table_name event_type row_data pk_data old_data trigger_hist_id channel_id transaction_id source_node_id external_data node_list create_time
    2190146 startsc U """6696282"",""2002"",""2002-05-29 00:00:00"",""GLD"",""USA"",""D"",""4"",""97034720"",""4353272"",""Switch My Pic"",""SWITCHMYPIC"",""903"",""JE"",""Houghton"",""T."",""D."",""HOUGHTON"",""T"",""D"",""28451"",""TE"",""Londono, Sr."",""Odin"",""J."",""LONDONOSR"",""ODIN"",""J"",""735172"",""O6"",""Saurbier"",""Marvin"","" "",""SAURBIER"",""MARVIN"","" "",""F"",""5"",""D"",""0"",""0"",""0"",""1074"",""1"",""2520.00"",""-1""" """6696282""" [NULL] 39 default 09:54:25:386.84 [NULL] [NULL] [NULL] 7/16/2014 9:54:25.736 AM
    2190147 startsc D [NULL] """6696282""" [NULL] 39 default 09:54:25:386.84 [NULL] [NULL] [NULL] 7/16/2014 9:54:25.740 AM
    2190148 startsc I """6696510"",""2002"",""2002-05-29 00:00:00"",""GLD"",""USA"",""D"",""4"",""97034544"",""4353084"",""Silent Pic"",""SILENTPIC"",""903"",""JE"",""Houghton"",""T."",""D."",""HOUGHTON"",""T"",""D"",""28451"",""TE"",""Londono, Sr."",""Odin"",""J."",""LONDONOSR"",""ODIN"",""J"",""735172"",""O6"",""Saurbier"",""Marvin"","" "",""SAURBIER"",""MARVIN"","" "",""F"",""5"",""D"",""0"",""0"",""0"",""1074"",""1"",""2520.00"",""-1""" [NULL] [NULL] 39 default 09:54:25:386.84 [NULL] [NULL] [NULL] 7/16/2014 9:54:25.766 AM
    2190149 startsc U """6696510"",""2002"",""2002-05-29 00:00:00"",""GLD"",""USA"",""D"",""4"",""97034544"",""4353084"",""Silent Pic"",""SILENTPIC"",""903"",""JE"",""Houghton"",""T."",""D."",""HOUGHTON"",""T"",""D"",""28451"",""TE"",""Londono, Sr."",""Odin"",""J."",""LONDONOSR"",""ODIN"",""J"",""735172"",""O6"",""Saurbier"",""Marvin"","" "",""SAURBIER"",""MARVIN"","" "",""F"",""5"",""D"",""0"",""0"",""0"",""1074"",""1"",""2520.00"",""69""" """6696510""" [NULL] 39 default 09:54:25:386.84 [NULL] [NULL] [NULL] 7/16/2014 9:54:25.766 AM