All Forums UDA
depakjan 11 posts Joined 01/09
29 Jun 2009
Fast export in unix

/opt/teradata/client/bin/fexp <<EOP .LOGTABLE load_work.datastore_log1 ; .LOGON BOX/userid,password;.BEGIN EXPORT SESSIONS 20;.EXPORT OUTFILE $path MODE RECORD FORMAT TEXT;select trim(cast(PROC_YYMM_DT as char(5))) (title ''), cast('~' as char(1)),trim(cast(ACCNO as char(21))) (title ''), cast('~' as char(1)),(CASEWHEN ACC_CO_NO IS NULLTHEN CAST('?' AS CHAR(1))ELSE trim(CAST(ACC_CO_NO AS char(21)))END) (title ''), cast('~' as char(1)),trim(cast(ACC_APPSYS_ID as char(3))) (title ''), cast('~' as char(1)),(CASEWHEN ACC_CHFGL_MT_TR_AM IS NULLTHEN CAST('?' AS CHAR(1))ELSE trim(CAST(ACC_CHFGL_MT_TR_AM as char(18)))END) (title ''), cast('~' as char(1)),(CASEWHEN ACC_CHFGL_LT_TR_AM IS NULLTHEN CAST('?' AS CHAR(1))ELSE trim(CAST(ACC_CHFGL_LT_TR_AM as char(18)))END) (title ''),cast('~' as char(1)),(CASEWHEN ACC_RECGL_LT_TR_AM IS NULLTHEN CAST('?' AS CHAR(1))ELSE trim(CAST(ACC_RECGL_LT_TR_AM as char(18)))END) (title ''),cast('~' as char(1)),(CASEWHEN BNKRPT_NOTIFTN_DT IS NULLTHEN CAST('?' AS CHAR(1))ELSE CAST(CAST(BNKRPT_NOTIFTN_DT AS FORMAT 'YYYY-MM-DD')AS CHAR(10))END) (title '')from vrtl.core_rcvrywhereproc_yymm_dt = 10811 andacc_appsys_id in ('599');.END EXPORT ; .LOGOFF;EOP~ as delimiteri am using the following query to fast export.. but i getting some junk characters prefixed to each column value ... what is the problem??.... it queries perfectly in teradata though..

robpaller 159 posts Joined 05/09
29 Jun 2009

That is the result of the variable length offset's being placed there by FastLoad. You can strip them out as a post process or concatenate the fields and delimiter together and cast it to a fixed length.Try this:select CAST( PROC_YYMM_DT || '~' || ACCNO || '~' || (CASE WHEN ACC_CO_NO IS NULL THEN '?' ELSE ACC_CO_NO END) || '~' || ACC_APPSYS_ID || '~' || (CASE WHEN ACC_CHFGL_MT_TR_AM IS NULL THEN '?' ELSE ACC_CHFGL_MT_TR_AM END) || '~' || (CASE WHEN ACC_CHFGL_LT_TR_AM IS NULL THEN '?' ELSE ACC_CHFGL_LT_TR_AM END) || '~' || (CASE WHEN ACC_RECGL_LT_TR_AM IS NULL THEN '?' ELSE ACC_RECGL_LT_TR_AM END) (CASE WHEN BNKRPT_NOTIFTN_DT IS NULL THEN '?' ELSE CAST(CAST(BNKRPT_NOTIFTN_DT AS FORMAT 'YYYY-MM-DD') AS CHAR(10)) END) AS CHAR( ) (TITLE '')from vrtl.core_rcvrywhereproc_yymm_dt = 10811 andacc_appsys_id in ('599');

You must sign in to leave a comment.