Unnecessary characters at beginning of record in file

Tools & Utilities

Unnecessary characters at beginning of record in file

Hi Everyone,

I am using the following Fast Export script and SQL query. I have pasted the output file below. Please let me know why am getting characters ! or %" at the beginning of the record if I insert spaces in FIRST_NAME column in Teradata table.

FYI: OREPLACE is a UDF similar to that of replace function in Oracle.

PM_HOME='/export/home/pp_adm/bin'
PM_OUT='/export/home/pp_adm/bin/inbound'
PM_INPUT='/export/home/pp_adm/bin/vonto'
PM_LOG='/export/home/pp_adm/bin/log'

LON=`cat $PM_INPUT/tdat.python.pp_mercury_dev.logon`
SQL=`cat $PM_INPUT/$2`
fexp < $PM_LOG/$1.log 2>&1
.LOGTABLE PP_SCRATCH.fexp_DBQLOBJ_$1;
$LON
.BEGIN EXPORT sessions 16;
$SQL
.EXPORT MODE INDICATOR OUTFILE $PM_OUT/$1.out FORMAT FASTLOAD;
.END EXPORT;
.LOGOFF;
EOF

SQL Query:

SELECT PP_SCRATCH.OREPLACE(CUST.CUSTOMER_FIRST_NAME,'|','')||'|'||
PP_SCRATCH.OREPLACE(CUST.CUSTOMER_LAST_NAME,'|','')||'|'||
(CASE
WHEN ADDR.ADDRESS_POSTAL_CODE='' THEN '99999'
WHEN ADDR.ADDRESS_POSTAL_CODE IS NULL THEN '99999'
WHEN PP_SCRATCH.OREPLACE(ADDR.ADDRESS_POSTAL_CODE,'|','')='' THEN '99999'
ELSE
PP_SCRATCH.OREPLACE(ADDR.ADDRESS_POSTAL_CODE,'|','')
END)
FROM PP_SCRATCH.DW_CUSTOMER CUST
,PP_SCRATCH.DW_ADDRESS ADDR
,PP_SCRATCH.DW_TABLE_CURRENT TBL_CURR
WHERE CUST.CUSTOMER_ID = ADDR.CUSTOMER_ID
AND CUST.CUSTOMER_FIRST_NAME IS NOT NULL
AND CUST.CUSTOMER_LAST_NAME IS NOT NULL
AND PP_SCRATCH.OREPLACE(CUST.CUSTOMER_FIRST_NAME,'|','')<>''
AND PP_SCRATCH.OREPLACE(CUST.CUSTOMER_LAST_NAME,'|','')<>''
AND CUST.CUSTOMER_FIRST_NAME <> ''
AND CUST.CUSTOMER_LAST_NAME <> ''
AND TBL_CURR.TABLE_NAME='DW_VONTU_FULL'
AND (CUST.CRE_DATE > TBL_CURR.CRE_DATE
OR CUST.UPD_DATE > TBL_CURR.UPD_DATE);

Output File:
FIRST_NAME|LAST_NAME|ZIP
VIJAY|VIJAYKUMAR|66 634
# ARAVIND|VIJAYKUMAR| 66634
VIJAY|kumar|99999
PRA SHANTH|vsn|123456
!cha lam|minish|1234567
!VIJ AY KU MAR|vsn|99999
VIJAY|VIJAY KUMAR|66634
VIJAY1|kumar1|123456
VIJAYkumar|VIJAY|99999
%" VIJ AY KU MAR|vsn|99999
!VIJAY KUMAR|VIJAY |99999
VIJAYkumar|VIJAY|99999
!VIJAY kumar|VIJAY |99999
VIJAY KUMAR|vsn|99999