Ambiguous results by fastexport..

Tools
Enthusiast

Ambiguous results by fastexport..

Hi
I am running fastexport with various combinations and getting different results which i am unable to understand

1).EXPORT OUTFILE 'C:\Documents and Settings\Desktop\test5.txt' format text mode record;
SELECT

(coalesce(Wrls,'') (char(10)))
||(coalesce(Wrls,'') (char(15)))

FROM
B
SAMPLE 1;

OUTPUT---SA-ABCDGroup Mechanics

2).EXPORT OUTFILE 'C:\Documents and Settings\Desktop\test5.txt' format text mode record;

SELECT

(coalesce(Wrls,'') (char(10))) ||','
||(coalesce(Wrls,'') (char(15)))

FROM
B
SAMPLE 1;
OUTPUT----> --SA-ABCD,Group Mechanics(junk characters at beginning)
3)
.EXPORT OUTFILE 'C:\Documents and Settings\Desktop\test5.txt' format text mode record;

SELECT

(COALESCE(TRIM(Wrls) , ' ')) ||','||(coalesce(TRIM(Wrls) ,''))

FROM
B
SAMPLE 1;

Output-->SA-ABCD,Group Mechanics

Basically it seems like
(COALESCE(TRIM(Wrls_Actvty_Cd) , ' '))=(coalesce(Wrls_Actvty_Cd,'') (char(10)))
But how?i am not able to understand...

Please explain me if i have five cols in my table
1)integer
2)date
3)character
4)varchar
5)numeric

Please explain what data conversions we need to do at time of exporting the file...
3 REPLIES
Enthusiast

Re: Ambiguous results by fastexport..

U CAN USE AS (CHAR) DTATYPE... AND GIVE CORRECT LENGTH OF THE FIELD..

FOR EXAMPLE DEC(11,0)-SHOULD BE CHAR13
DEC(13,0) -CHAR15
SMALL INT -CHAR6
INTEGER-CHAR10
varchar(30)-char30

it is differ from linux and mainframe
Enthusiast

Re: Ambiguous results by fastexport..

Hi Sheeba,
Thanks for ur response..
But still i have some doubts
as u can see below
.EXPORT OUTFILE 'C:\Documents and Settings\Desktop\test5.txt' format text mode record;

SELECT

(COALESCE(TRIM(Wrls) , ' ')) ||','||(coalesce(TRIM(Wrls) ,''))

FROM
B
SAMPLE 1;

Output-->SA-ABCD,Group Mechanics

and when i am running
.EXPORT OUTFILE 'C:\Documents and Settings\Desktop\test5.txt' format text mode record;

SELECT

(COALESCE(TRIM(Wrls) , ' ')) ||(coalesce(TRIM(Wrls) ,''))

FROM
B
SAMPLE 1;

Output-->!SA-ABCD,Group Mechanics junk characters are coming
I am unable to understand this phenomenon ...
Could u pls elaborate on this
Junior Contributor

Re: Ambiguous results by fastexport..

FORMAT TEXT doesn't mean it's actually characters.
Those are no junk characters, but a two byte unsigned integer indicating the length of the following VarChar.

If you use any literal it's a VarChar (just don't ask me why, i think it's stupid, because it's always the same string) and concatenating Chars and VarChars yields VarChar, just try a
SELECT
TYPE((COALESCE(TRIM(Wrls) , ' ')) ||','||(coalesce(TRIM(Wrls) ,'')))
FROM
B
SAMPLE 1;

Dieter