passing parameter to mload script and loading into target table

Database

passing parameter to mload script and loading into target table

Do I need to define the column in file format section of mload which I am planning to load as hard coded value coming from parameter file. In attached code report_suit_id should be passed from parameter file as &val4 but I am getting syntax error saying that "Syntax error: expected something between '(' and the end of the request.". If I hardcode the report_suit_id as '1', its working fine. Please let me know how can I pass this value from parameter file. I need to perform case when based on value coming from parameter file for this field.


.LOGTABLE DEV_ETL_WORK.ML_adobe_dw_daily_agg;

.ACCEPT var1,var2,var3,var4 FROM FILE /home/rt/param.txt ;

.LOGON &var1/&var2,&var3;

DROP TABLE DEV_ETL_WORK.ET_adobe_dw_daily_agg ;

DROP TABLE DEV_ETL_WORK.UV_adobe_dw_daily_agg ;

DROP TABLE DEV_ETL_WORK.WT_adobe_dw_daily_agg ;

.BEGIN IMPORT MLOAD 

 TABLES DEV_MARKETING_STAGE.adobe_dw_daily_agg

 WORKTABLES DEV_ETL_WORK.WT_adobe_dw_daily_agg

ERRORTABLES DEV_ETL_WORK.ET_adobe_dw_daily_agg DEV_ETL_WORK.UV_adobe_dw_daily_agg

ERRLIMIT          0

CHECKPOINT      10000

TENACITY 10000

SESSIONS        1

SLEEP 6


/* Begin Layout Section */ 

.Layout InputFileLayout;

.Field  HOURx                                   * VARCHAR(255)  ;

.Field  site_name                                  * VARCHAR(255)  ;

.Field  site_mobile_category                       * VARCHAR(255)   ;

.Field  site_type                                  * VARCHAR(255)   ;

.Field  site_section                               * VARCHAR(255)   ;

.Field  new_return_visits                          * VARCHAR(255)   ;

.Field  site_country                               * VARCHAR(255)   ;

.Field  site_language                              * VARCHAR(255)   ;

.Field  loginstatus                                * VARCHAR(255)   ;

.DML Label tagDML;

INSERT INTO DEV_MARKETING_STAGE.adobe_dw_daily_agg ( 

report_suite_id,

fact_time                                   , 

site_name                               , 

site_mobile_category                    , 

site_type                               , 

site_section                            , 

new_return_visits                       , 

site_country                            , 

site_language                           , 

loginstatus                             

) VALUES ( 

&val4,

CAST(CAST(TO_TIMESTAMP(OREPLACE(trim(:hourx),'Hour',''),'MONTH dd,yyyy,HH24') AS VARCHAR(19)) AS TIMESTAMP(0)),

:site_name                              , 

:site_mobile_category                   , 

:site_type                              , 

:site_section                           , 

:new_return_visits                      , 

:site_country                           , 

:site_language                          , 

:loginstatus                           

) ; 

 .Import Infile '/home/rtapdiya/rt1.csv'

 Layout InputFileLayout

 format vartext  ',' QUOTE OPTIONAL '"'

 Apply tagDML 

;

.END MLOAD;

.LOGOFF;

Tags (2)
3 REPLIES

Re: passing parameter to mload script and loading into target table

Has anyone tried or done this before?

Junior Supporter

Re: passing parameter to mload script and loading into target table

Hi.

Please provide the table DDL (at least the column datatypes) and the param.txt file.

Cheers.

Carlos.

Re: passing parameter to mload script and loading into target table

looks like you have used val4 in the insert statement instead of var4 in the script.