junk characters when trimming char in fast export

Tools
N/A

junk characters when trimming char in fast export

I'm trying to do a fast export and trimming the statement but I keep getting junk chracters in the beginning. ent_cust_id is a decimal 12,0 so I'm converting it to a char.

This way work but I get too many blank spaces so the size of the file is fairly large, about 10gb.

.LOGTABLE cassLog;
.LOGON td/user,pass;

.BEGIN EXPORT SESSIONS 20;

.EXPORT OUTFILE entcust.csv
MODE RECORD
FORMAT TEXT;

SELECT cast(
CAST(CAST(ENT_CUST_ID AS FORMAT '99999999999') AS CHAR(11)) || ',"'
|| TRIM(COALESCE(CAST(list AS CHAR(235)),'')) || '"' AS char(250))
from cassImport2;

.END EXPORT;

.LOGOFF;

If I try to do it this way, I get junk characters in the begining of some lines:

.LOGTABLE cassLog;
.LOGON td/user,pass;

.BEGIN EXPORT SESSIONS 20;

.EXPORT OUTFILE entcust.csv
MODE RECORD
FORMAT TEXT;

SELECT trim(cast(
CAST(CAST(ENT_CUST_ID AS FORMAT '99999999999') AS CHAR(11)) || ',"'
|| TRIM(COALESCE(CAST(list AS CHAR(235)),'')) || '"' AS char(250)))
from cassImport2;

.END EXPORT;

.LOGOFF;

When I trim the entire string, the file size goes down to about 2gb.

1 REPLY

Re: junk characters when trimming char in fast export

Hi.

TRIM() returns VARCHAR type, thus adding an integer value with the lenght of the string at the beginning of each line. These are your 'junk characters'.

Cheers.

Carlos.