Invalid FORMAT string and "MJ" at the begining of the file

Tools

Invalid FORMAT string and "MJ" at the begining of the file

Hi everyone,

I have the following fast export script.

#!/usr/bin/ksh
LON=`cat $HOME/bin/fexp_dbql/tdat.python.pp_dev.logon`
fexp < $HOME/bin/fexp_dbql/logs/dbqlobj_qc_vontu_full.log 2>&1
.LOGTABLE PP_working_dev.fexp_DBQLOBJ_qc_vontu_full;
$LON

.BEGIN EXPORT sessions 16;

SELECT

cast('WCUSTOMER' as char(10))||'|'||cast(count(*) as char(10))||'|'||cast(('UPD_DATE=') as char(50))||cast(cast(max(A.UPD_DATE) as date FORMAT 'yyyy-mm-dd hh:mi:ss') as char(20))
FROM
PP_TABLES.DW_CUSTOMER A, PP_TABLES.DW_ADDRESS B
WHERE
A.CUSTOMER_ID=B.CUSTOMER_ID
;

.EXPORT MODE INDICATOR OUTFILE $1/qc_wcustomer_vontu_full.out FORMAT FASTLOAD;

.END EXPORT;
.LOGOFF;
EOF

When I executed this, I am getting the following error message.Also my output contains MJ at the begining of the file.

Invalid FORMAT string 'yyyy-mm-ddhh:mi:ss'

When I delete the last format cast(cast(max(A.UPD_DATE) as date FORMAT 'yyyy-mm-dd hh:mi:ss') as char(20)), I am getting "MJ" at the beginning of the file.

Please someone help me out.

Thanks in adavance...

1 REPLY
Senior Apprentice

Re: Invalid FORMAT string and "MJ" at the begining of the file

Hi Prasanth,

1. You have to cast to a timestamp instead of a date:
cast(max(A.UPD_DATE) as date FORMAT 'yyyy-mm-dd hh:mi:ss')

2. '|' is a VARCHAR, concatenating CHARs and VARCHARs results in a VARCHAR.
MJ is probably the two word indication the length of the VARCHAR.

Use "cast('|' as char(1))"

Dieter