Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 26 May 2009 @ 10:37:07 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   Perl and Invalid SQL statement for MLOAD
 
From:   Giulio Bertani

Hello to All,

sorry for my bad english I hope will be enought clear:

-- CONTEST:

I'm using a Teradata V2R5.1 with Perl driver.

I've just donwload a Table in a file using code example in documentation with driver.


-- PROBLEM:

I'm trying to upload a file using MLOAD Perl interface.

But I can't understand why I always get a generic Teradata SQL Message:

UtilitySetup: Invalid SQL statement for MLOAD.


Will be apreciate if it's possible to understand better the "syntax" problem in some way.


Here the simplified "code" :

     my $ctldbh = DBI->connect(
          'dbi:Teradata:'.$server.';database=jedi_kbo_db'
          , $username
          , $password
          , {  RaiseError   => 0
             , PrintError   => 0
             , tdat_lsn     => 0
             , tdat_no_cli  => 'NO'
             , tdat_mode    => 'DEFAULT'
             , tdat_charset => 'ASCII'
     #       , trace        => 2
             }
                               ) || die "NOT CONNECTED !!!";
     # THIS WORKS ....

     # HERE THE MLOAD ISTRUCTION:
     my $total = $ctldbh->tdat_UtilitySetup(
         {
             Utility      => 'MLOAD',
             Sessions     => $sesscount,
             SQL          => [ "$select" ] ,
             SourceFields => $sourceFields,
             Source       => "VARTEXT '|' ".$filenameF ,
             MP => 1
         });

     print "PROBLEM!!!!:\n"
     ."str:".$ctldbh->errstr."\n"
     ."err:".$ctldbh->err."\n"
     .$DBI::errstr."\n"
         unless ($total && ($total > 0));

     # HERE THE ROWS FOR $select and $sourceFields
     # (To put before command )

     my $select    = $opt_B_Sel || <<"ENDSELECT";
     IGNORE DUPLICATE INSERT ROWS;
     INSERT INTO PARTY (
       bus_duns_no                    = :f_bus_duns_no
     , bus_fed_tax_id                 = :f_bus_fed_tax_id
     , bus_legal_class_cd             = :f_bus_legal_class_cd
     , bus_legal_class_ds             = :f_bus_legal_class_ds
     , bus_main_objct_ds              = :f_bus_main_objct_ds
     , bus_pub_priv_co_in             = :f_bus_pub_priv_co_in
     , bus_stock_symb_cd              = :f_bus_stock_symb_cd
     , bus_vat_no                     = :f_bus_vat_no
     , call_class_id                  = :f_call_class_id
     , delete_in                      = :f_delete_in
     , insert_dt                      = :f_insert_dt
     , last_update_dt                 = :f_last_update_dt
     , party_accept_var_pct           = :f_party_accept_var_pct
     , party_amps_in                  = :f_party_amps_in
     , party_assoc_ds                 = :f_party_assoc_ds
     , party_atc_ds                   = :f_party_atc_ds
     , party_bnl_relation_in          = :f_party_bnl_relation_in
     , party_bt_relation_in           = :f_party_bt_relation_in
     , party_carr_type_ds             = :f_party_carr_type_ds
     , party_consctd_ds               = :f_party_consctd_ds
     , party_correlatd_cd             = :f_party_correlatd_cd
     , party_correlatd_eff_dt         = :f_party_correlatd_eff_dt
     , party_correlatd_end_dt         = :f_party_correlatd_end_dt
     , party_correlatd_in             = :f_party_correlatd_in
     , party_cred_am                  = :f_party_cred_am
     , party_cred_owner_nm            = :f_party_cred_owner_nm
     , party_ds                       = :f_party_ds
     , party_eni_relation_in          = :f_party_eni_relation_in
     , party_exclude_cred_check_in    = :f_party_exclude_cred_check_in
     , party_exp_am                   = :f_party_exp_am
     , party_export_pct               = :f_party_export_pct
     , party_ext_src_ds               = :f_party_ext_src_ds
     , party_frode_in                 = :f_party_frode_in
     , party_grp_ass_id               = :f_party_grp_ass_id
     , party_id                       = :f_party_id
     , party_import_pct               = :f_party_import_pct
     , party_indiv_birth_dt           = :f_party_indiv_birth_dt
     , party_indiv_gender_cd          = :f_party_indiv_gender_cd
     , party_indiv_social_secur_no    = :f_party_indiv_social_secur_no
     , party_isp_in                   = :f_party_isp_in
     , party_lead_by_ds               = :f_party_lead_by_ds
     , party_lead_dt                  = :f_party_lead_dt
     , party_loc_no                   = :f_party_loc_no
     , party_med_relation_in          = :f_party_med_relation_in
     , party_org_abroad_site_in       = :f_party_org_abroad_site_in
     , party_org_bus_in               = :f_party_org_bus_in
     , party_org_clerk_no             = :f_party_org_clerk_no
     , party_org_emp_no               = :f_party_org_emp_no
     , party_org_exp_dest_ds          = :f_party_org_exp_dest_ds
     , party_org_exp_in               = :f_party_org_exp_in
     , party_org_imp_dest_ds          = :f_party_org_imp_dest_ds
     , party_org_imp_in               = :f_party_org_imp_in
     , party_org_legal_dt             = :f_party_org_legal_dt
     , party_org_reg_loc_nm           = :f_party_org_reg_loc_nm
     , party_org_reg_no               = :f_party_org_reg_no
     , party_org_svc_org_in           = :f_party_org_svc_org_in
     , party_org_type_cd              = :f_party_org_type_cd
     , party_org_url_ds               = :f_party_org_url_ds
     , party_org_wrk_no               = :f_party_org_wrk_no
     , party_orignl_atlanet_cd        = :f_party_orignl_atlanet_cd
     , party_orignl_cd                = :f_party_orignl_cd
     , party_outsourcing_in           = :f_party_outsourcing_in
     , party_preact_in                = :f_party_preact_in
     , party_rvnu_dt                  = :f_party_rvnu_dt
     , party_rvnu_growth_pct          = :f_party_rvnu_growth_pct
     , party_soph_lvl_no              = :f_party_soph_lvl_no
     , party_src_ds                   = :f_party_src_ds
     , party_src_list_cd              = :f_party_src_list_cd
     , party_tam_farming_ds           = :f_party_tam_farming_ds
     , party_tam_origin_ds            = :f_party_tam_origin_ds
     , party_tax_mode_ds              = :f_party_tax_mode_ds
     , party_tlc_data_am              = :f_party_tlc_data_am
     , party_tlc_exp_dom_pct          = :f_party_tlc_exp_dom_pct
     , party_tlc_exp_inter_pct        = :f_party_tlc_exp_inter_pct
     , party_tlc_exp_intnl_pct        = :f_party_tlc_exp_intnl_pct
     , party_tlc_exp_urb_pct          = :f_party_tlc_exp_urb_pct
     , party_tlc_exp_wirels_pct       = :f_party_tlc_exp_wirels_pct
     , party_type_cd                  = :f_party_type_cd
     , party_yr_rvnu_am               = :f_party_yr_rvnu_am
     , src_id                         = :f_src_id
     , unit_of_measure_id             = :f_unit_of_measure_id
     );
     ENDSELECT



     my $sourceFields = <<"ENDSOURCEFIELDS";
     USING (
       f_bus_duns_no                 varchar(30)
     , f_bus_fed_tax_id              varchar(16)
     , f_bus_legal_class_cd          char(6)
     , f_bus_legal_class_ds          varchar(30)
     , f_bus_main_objct_ds           varchar(100)
     , f_bus_pub_priv_co_in          char(1)
     , f_bus_stock_symb_cd           char(6)
     , f_bus_vat_no                  varchar(11)
     , f_call_class_id               byteint
     , f_delete_in                   char(1)
     , f_insert_dt                   date
     , f_last_update_dt              date
     , f_party_accept_var_pct        byteint
     , f_party_amps_in               char(1)
     , f_party_assoc_ds              varchar(100)
     , f_party_atc_ds                varchar(100)
     , f_party_bnl_relation_in       char(1)
     , f_party_bt_relation_in        char(1)
     , f_party_carr_type_ds          varchar(50)
     , f_party_consctd_ds            varchar(100)
     , f_party_correlatd_cd          varchar(50)
     , f_party_correlatd_eff_dt      date
     , f_party_correlatd_end_dt      date
     , f_party_correlatd_in          char(1)
     , f_party_cred_am               decimal(18,4)
     , f_party_cred_owner_nm         varchar(50)
     , f_party_ds                    varchar(255)
     , f_party_eni_relation_in       char(1)
     , f_party_exclude_cred_check_in char(1)
     , f_party_exp_am                decimal(13,6)
     , f_party_export_pct            decimal(3,0)
     , f_party_ext_src_ds            varchar(50)
     , f_party_frode_in              char(1)
     , f_party_grp_ass_id            integer
     , f_party_id                    integer
     , f_party_import_pct            decimal(3,0)
     , f_party_indiv_birth_dt        date
     , f_party_indiv_gender_cd       char(6)
     , f_party_indiv_social_secur_no char(9)
     , f_party_isp_in                char(1)
     , f_party_lead_by_ds            varchar(100)
     , f_party_lead_dt               date
     , f_party_loc_no                integer
     , f_party_med_relation_in       char(1)
     , f_party_org_abroad_site_in    char(1)
     , f_party_org_bus_in            char(1)
     , f_party_org_clerk_no          decimal(5,0)
     , f_party_org_emp_no            decimal(18,4)
     , f_party_org_exp_dest_ds       varchar(255)
     , f_party_org_exp_in            char(1)
     , f_party_org_imp_dest_ds       varchar(255)
     , f_party_org_imp_in            char(1)
     , f_party_org_legal_dt          date
     , f_party_org_reg_loc_nm        varchar(30)
     , f_party_org_reg_no            varchar(30)
     , f_party_org_svc_org_in        char(1)
     , f_party_org_type_cd           char(6)
     , f_party_org_url_ds            varchar(100)
     , f_party_org_wrk_no            integer
     , f_party_orignl_atlanet_cd     varchar(100)
     , f_party_orignl_cd             varchar(40)
     , f_party_outsourcing_in        char(1)
     , f_party_preact_in             char(1)
     , f_party_rvnu_dt               date
     , f_party_rvnu_growth_pct       decimal(3,0)
     , f_party_soph_lvl_no           varchar(30)
     , f_party_src_ds                varchar(50)
     , f_party_src_list_cd           char(6)
     , f_party_tam_farming_ds        varchar(255)
     , f_party_tam_origin_ds         varchar(2000)
     , f_party_tax_mode_ds           varchar(50)
     , f_party_tlc_data_am           decimal(18,6)
     , f_party_tlc_exp_dom_pct       decimal(18,4)
     , f_party_tlc_exp_inter_pct     decimal(18,4)
     , f_party_tlc_exp_intnl_pct     decimal(18,4)
     , f_party_tlc_exp_urb_pct       decimal(18,4)
     , f_party_tlc_exp_wirels_pct    decimal(18,4)
     , f_party_type_cd               char(6)
     , f_party_yr_rvnu_am            decimal(18,6)
     , f_src_id                      byteint
     , f_unit_of_measure_id          integer
     )
     ENDSOURCEFIELDS

Any help will be apreciate, thanks in advance

Giulio

Giulio Bertani



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016