Insert without columns with default value

[giuseppe_moglia5]

Hi Guys

from : INSERT - SAP HANA SQL and System Views Reference - SAP Library

"A column which is not included in the column list will be filled using the columns default value."

but I have this table :

CREATE COLUMN TABLE "SBODEMOIT"."flusso_arpp" ("arpp_c_part" VARCHAR(30),

  "arpp_dt_prior" LONGDATE CS_LONGDATE,

  "arpp_tst_comm" VARCHAR(3) NOT NULL ,

  "arpp_prf_comm" VARCHAR(1) NOT NULL ,

  "arpp_a_comm" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_n_comm" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_n_riga_comm" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_c_ditta" VARCHAR(2) NOT NULL ,

  "arpp_c_vers_db" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_n_ciclo" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_n_fase" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_liv" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_t_oper" VARCHAR(4),

  "arpp_n_r_padre" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_t_c_part" VARCHAR(1),

  "arpp_flg_alter" VARCHAR(1),

  "arpp_liv_impl" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_desc" VARCHAR(2000),

  "arpp_note" VARCHAR(300),

  "arpp_c_prov" VARCHAR(1),

  "arpp_t_aliq" VARCHAR(1),

  "arpp_qta_uni" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_qta_tot" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_qta_xvend" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_qta_scgl" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_qta_rif" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_t_fatt_conv" VARCHAR(1),

  "arpp_fatt_conv" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_qta_uni_uma" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_qta_tot_uma" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_lt_gg" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_lt_run_sec" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_cdl" VARCHAR(12),

  "arpp_dt_disp_ris" LONGDATE CS_LONGDATE,

  "arpp_dt_disp_calc" LONGDATE CS_LONGDATE,

  "arpp_dt_disp" LONGDATE CS_LONGDATE,

  "arpp_dt_progr" LONGDATE CS_LONGDATE,

  "arpp_dt_fabb" LONGDATE CS_LONGDATE,

  "arpp_tmavar_gg" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_flg_completo" VARCHAR(1),

  "arpp_flg_ant" VARCHAR(1),

  "arpp_ant_sec" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_flg_critico" VARCHAR(1),

  "arpp_flg_dest" VARCHAR(1),

  "arpp_flg_t_lotto" VARCHAR(1),

  "arpp_c_lotto" VARCHAR(20),

  "arpp_esp_rev_comm" VARCHAR(2),

  "arpp_dt_rev_comm" LONGDATE CS_LONGDATE,

  "arpp_esp_rev_db" VARCHAR(2),

  "arpp_rif_disegno" VARCHAR(16),

  "arpp_rif" VARCHAR(60),

  "arpp_classe_ricambi" VARCHAR(1),

  "arpp_qta_ricambi" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_flg_varliv" VARCHAR(1),

  "arpp_c_unicod" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_flg_ctrl_leg" VARCHAR(1),

  "arpp_ordinatore" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_um_1" VARCHAR(3),

  "arpp_um_2" VARCHAR(3),

  "arpp_flg_sttcomm" VARCHAR(1),

  "arpp_c_sttcomm" VARCHAR(30),

  "arpp_dt_ins" LONGDATE CS_LONGDATE,

  "arpp_utente_ins" VARCHAR(5),

  "arpp_dt_um" LONGDATE CS_LONGDATE,

  "arpp_utente_um" VARCHAR(5),

  "arpp_flg_status" VARCHAR(1),

  "arpp_gg_anticipo" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_flg_cpart_da_comm" VARCHAR(1),

  "arpp_c_part_alt" VARCHAR(30),

  "arpp_dim_1" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_dim_2" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_dim_3" DECIMAL CS_DECIMAL_FLOAT DEFAULT 0 NOT NULL ,

  "arpp_dt_prec" LONGDATE CS_LONGDATE,

  "arpp_flg_conf" VARCHAR(1),

  "arpp_esp_rev_part" VARCHAR(2),

  PRIMARY KEY ("arpp_tst_comm",

  "arpp_prf_comm",

  "arpp_a_comm",

  "arpp_n_comm",

  "arpp_n_riga_comm",

  "arpp_c_ditta")) UNLOAD PRIORITY 5 AUTO MERGE

and I try this inisert:

insert into "flusso_arpp" (

  "arpp_c_part" ,

  "arpp_dt_prior" ,

  "arpp_tst_comm" ,

  "arpp_prf_comm" ,

  "arpp_a_comm" ,

  "arpp_n_comm" ,

  "arpp_n_riga_comm" ,

  "arpp_c_ditta" ,

  "arpp_c_vers_db" ,

  "arpp_n_ciclo" ,

  "arpp_n_fase" , a

  "arpp_liv" ,

  "arpp_n_r_padre" ,

  "arpp_t_c_part" ,

  "arpp_liv_impl" ,

  "arpp_desc" ,

  "arpp_c_prov" , "arpp_t_aliq" , "arpp_qta_uni" , "arpp_qta_tot" , "arpp_qta_xvend" , "arpp_qta_scgl" , "arpp_qta_rif" , "arpp_fatt_conv" , "arpp_qta_uni_uma" , "arpp_qta_tot_uma" , "arpp_lt_gg" , "arpp_lt_run_sec" , "arpp_dt_fabb" , "arpp_tmavar_gg" , "arpp_ant_sec" , "arpp_flg_dest" , "arpp_qta_ricambi" , "arpp_c_unicod" , "arpp_ordinatore" , "arpp_um_1" , "arpp_dt_ins" , "arpp_utente_ins" , "arpp_dt_um" , "arpp_utente_um" , "arpp_flg_status" , "arpp_gg_anticipo" , "arpp_flg_cpart_da_comm" , "arpp_rif" , "arpp_rif_disegno" ) values ( 'LM4029SB-0001' , {ts '2016-07-27 00:00:00.000000'} , 'COV' , 'A' , 2016 , 83 , 80 , '01' , null , 0 , 0 , 1 , 0 , 'C' , 0 , 'Scheda Madre Stampante LM4029' , '2' , 'N' , 0.030303030303030303030303030303 , 1 , 0 , 1 , 1 , 0 , 0 , 0 , 0 , 0 , {ts '2016-07-27 00:00:00.000000'} , 0 , 0 , 'N' , 0 , 0 , 8 , 'N' , {ts '2016-07-18 10:39:09.965000'} , '1' , {ts '2016-07-18 10:39:09.965000'} , '1' , 'A' , 0 , 'N' , null , '' )

And I receive this error:

Could not execute 'insert into "flusso_arpp" ( "arpp_c_part" , "arpp_dt_prior" , "arpp_tst_comm" , "arpp_prf_comm" , ...' in 3 ms 777 µs .

SAP DBTech JDBC: [287]: cannot insert NULL or update to NULL: arpp_c_vers_db

Someone can explain me why please?

Thanks

[former_member182967 · Accepted solution]

Hi Giuseppe,

You have defined that the field arpp_c_vers_db is not null in create table statement, but you give a NULL value in insert value sequence.

Regards,

Ning

[giuseppe_moglia5 · Accepted solution]

thanks Ning

my mistake

[cicero1988 · Accepted solution]

ello Ning Tong, how are you? hope so ! I am trying to perform an insert in a table in the Oracle database mapped by the SDA, when performing the insert I have the following error 'internal error: Error executing query [Oracle] [ODBC] Invalid datetime format'. CREATE ROW TABLE ( "CHAVE" DECIMAL(20) CS_FIXED NOT NULL, "PROCESSADO" NVARCHAR CS_STRING NOT NULL, "TIPO" NVARCHAR CS_STRING NOT NULL, "ERRO" NVARCHAR(4000) CS_STRING, "DATA_GRAVACAO" LONGDATE CS_LONGDATE ) INSERT into TABLE( CHAVE,PROCESSADO,TIPO,ERRO,DATA_GRAVACAO ) VALUES ( '00000000000000126643','F','I',MAP('@','@',NULL,'@'),TO_DATE(MAP('20201001','00000000',NULL,'20201001' ),'YYYYMMDD'))

[former_member182967 · Accepted solution]

[Oracle] [ODBC] Invalid datetime format, could you please check Oracle document about the correct datetime format?