Extra junk spaces in the bteq export file

Tools & Utilities

Extra junk spaces in the bteq export file

Hi
I am trying to export some fields to out files for data comparison purpose. Though the values get exported, they are getting padded with extra spaces to the full size of filed. (e.g. for datatype Varchar2(200), the field is getting filled with additional spaces in addition to the data values.). There by the comparision by unix diff method fails the result (eventhough the values match).

The syntax i have followed is:
.export report file=$ROOT/Tgt_data.out
SELECT
trim(field1) (title ' '),
trim(field2) (title ' ')
----------
----------
FROM
WHERE
.export reset

Example output:
Suppose field1 is Varchar(100)
its a name field and value stored is 'satya'
=>output appears 'satya'

Please suggest.
2 REPLIES
Enthusiast

Re: Extra junk spaces in the bteq export file

give all the fields value ex

fiedl 1 varchar(100)
filed 2 char(100)
field3 interger- char(10)
small int -char(6)

add all value 100+100+10+6=216

SELECT CAST(CKSS01_MODNUM_R AS CHAR(11)) (title '')
,CAST(CKSS01_VERNUM_R AS CHAR(11)) (title '')
,CKSS01_MODEL_TP_C (CHAR(1)) (title '')
,CKSS01_SCRTYP_C (CHAR(1)) (title '')
,CKSS01_SCRTYP_X (CHAR(10)) (title '')
,CKSS01_MDLDESC_X (CHAR(100)) (title '')
,CKSS01_VERDESC_X (CHAR(100)) (title '')
,CKSS01_VEREFF_Y (FORMAT 'YYYY-MM-DD') (CHAR(10)) (title '')
,(CASE
WHEN CKSS01_VEREND_Y IS NULL
THEN CAST('9999-12-31' AS CHAR(10) )
ELSE CAST(CAST(CKSS01_VEREND_Y AS FORMAT 'YYYY-MM-DD')
AS CHAR(10))
END) (title '')
,CKSS01_MOD_CATEG_C (CHAR(2)) (title '')
,(CKSS01_MOD_CATEG_X||' ') (char(15)) (title '')
FROM MCKSS01_MODEL_TBL
ORDER BY 1,2
;
SN
Enthusiast

Re: Extra junk spaces in the bteq export file

hi,

try this for varchar2(200) field..

select
.......
trim((cast(varchar2 as char(200)))
.......
from tablename;

this should remove the trailing pad characters.

thx,