Control characters in BTEQ output

Tools & Utilities
KVB
Enthusiast

Control characters in BTEQ output

Control characters in Unix file from BTEQ script.Moreover,control characters are common in the file

I have the below data in the table KVB

empno,ename,gender

1,BHARATH,M

2,VENKAT,M

3,RADHA,F

#!/bin/ksh

bteq<<EOFInsSel

`cat /opt/idw/pr/common/cmd/idwpass`

.export data file=sha.txt

.set quiet on

.set width 1500

.set seperator '|'

select ename,empno,gender from kvb;

.EXPORT RESET

.logoff

.exit

My output looks like the below one with control characters and empno is missing

^N^@^G^@BHARATH^B^@^@^@M

^M^@^F^@VENKAT^A^@^@^@M

^L^@^E^@RADHA^C^@^@^@F

Regards

KVB

4 REPLIES
Junior Contributor

Re: Control characters in BTEQ output

Hi KVB,

you're exporting in binary format, the control chars are 2-byte SMALLINTs for the rcord length and the varchar length, try EXPORT REPORT instead.

Dieter

Teradata Employee

Re: Control characters in BTEQ output

Just use ".export file=sha.txt" instead of ".export data file=sha.txt".

See bteq manual on more details of the syntax being used in your script.

KVB
Enthusiast

Re: Control characters in BTEQ output

Thanks for your inputs.I got the below output.

    EMPNO  ENAME                 GENDER

-----------  --------------------  ------

          2  BHARATH               M

          1  VENKAT                M

          5  ?                     M

          3  RADHA                 F

can't i get something like  the below one.

2,BHARATH,M

1,VENKAT,M

........

regards

KVB

Junior Contributor

Re: Control characters in BTEQ output

Hi KVB,

you have to TRIM/CONCAT on your own:

select trim(ename)  || '|' |trim(empno) |'|' |gender (title '') from kvb;

And don't forget COALESCE when there are NULLable columns :-)

Dieter